As an Excel user, you may need to add start and end date columns to your spreadsheets. As such, Excel includes a number of functions that tell you how many days there are between two separate dates. There are four Excel functions you can add to cells that tell you how many days are between two specified dates. This article describes how to use Excel to calculate days between two specified dates with and without using these functions. Let’s start.
How to find the difference between dates in Excel without a function?
First, you can find the difference between dates by subtracting them. Excel doesn’t include a subtraction function, but you can still add subtraction formulas to your cells. Here’s how to do it for dates.
- Open a “blank” Excel spreadsheet and enter a . in “get started” and “end” date in cells “B4” and “C4” as in the snapshot below. Note that the dates must be in the US format, with the month first, the second day, and the third year.
- Now select cell “D4,” click in the “Formula bar” at the top, then enter “
=C4-B4” and press “Enter.” Cell “D4” returns a value of “34”. Note: “C4” comes first because you are subtracting.
How to find the difference between two dates in Excel using DATE function
You can find the difference between two dates using the DATE function. Then you can find the number of days by entering the dates in the function bar instead of in spreadsheet cells. The basic syntax for that function is: “=DATE(yyyy, m, d)-DATE(yyyy, m, d).” To calculate the difference correctly, the last date is inserted first.
- Select a “cell” on the spreadsheet where you want to add the function, and make sure it is set to “general” format.
- Click in the “function bar”, then type “
=DATE(2022, 5, 5)-DATE(2022, 4, 1)” and press “Enter.”
Find the difference between two dates using the DATEDIF function
DATEDIF is a flexible function that you can use to calculate the total number of days by entering dates in the worksheet or in the function bar. However, DATEDIF is not listed in Excel’s Insert Function window because it only exists for compatibility with Lotus 1-2-3 workbooks.
Also, using DATEDIF may produce incorrect results in some situations. See this Excel DATEIF help page for more information.
If you decide to use the “DATEIF” function, you must enter it directly into the function bar. The syntax includes the following: DATEDIF(startdate; enddate; unit). You can enter a start date and end date or cell references to specific dates in the function, and then add the unit of “days” to the end of it. Here’s how to do it.
- Select the “cell” on the spreadsheet where you want to place the function and set it to “general” format.
- To find the difference in days (including years) entered in cells B6 and C6, type “
=DATEDIF(B6, C6, "d")” in the function bar and press “Enter.” The “d” stands for the format “days”.
- if you must ignore years in the calculation replace “d” with “yd” so you get a formula listed as “
=DATEDIF(B4, C4, "yd").” She” exclusive year, but the “d” includes “to dawn.”
If you encounter any errors, make sure to insert the earliest cell first, which is “B6” above.
Find the difference between two dates with the DAYS360 function
The DAYS360 function is one that finds the total number of days between dates based on a 360-day calendar, which is more commonly used for fiscal years. As such, that might be a better account spreadsheet feature. It doesn’t matter much for dates that are only a few months apart, but for longer periods, DAYS360 will return slightly different values than the other functions.
- Enter ‘1/1/2021′ and ‘1/1/2022‘ in cells B6 and C6 on your spreadsheet.
- Then click on a cell to display the . to include DAYS360 function, press Formulas > Date & Time.
- Of the Date Time drop-down menu, select DAYS360.
- press the Start date button and type “B6,” Click on the End date and type in “C6” then press Okay.
- The DAYS360 function returns a value of 360.
Find the difference between two dates using the NETWORKDAYS function
What if you want to find the difference between two dates but exclude weekends from the equation? DATEDIF, DATE and DAYS360 will not bode well for such a scenario. NETWORK DAYS is a function that finds the number of days between dates without including weekends, and it can also take into account additional holidays, such as public holidays.
So it should be a function for project scheduling. The basic syntax of the function is: =NETWORKDAYS(start date, end date, [holidays]).
- Click a cell to which you want to add the function and press Formulas > Date & Time > NETWORK DAYS.
- Type “B7″ for the Start date and “C7” for the End date and click Okay.
- Using the start and end dates of 1/4/2022 and 5/5/2022, NETWORK DAYS function returns a value of 25 days between the dates excluding the weekends. Including weekends, the total number of days is 34 as in previous examples.
- To include additional vacation days in the function, enter those dates in additional spreadsheet cells. Then press the Vacations cell reference button on the NETWORK DAYS function pane and select the cell or cells that contain the holiday dates. That will subtract the holiday from the final figure.
So there are tons of ways you can calculate days between start and end dates in Excel spreadsheets. More recent Excel versions also include a TO DAWN function that allows you to find the difference between a few dates. Those features will definitely come in handy for spreadsheets with lots of dates.
Frequently Asked Questions
What does #NUM mean?
When you run the above formulas and receive #NUM instead of a number, it’s because the start date is greater than the end date. Try flopping the dates around and doing the steps again.