How to calculate days between two dates in Excel

Many Excel users will need to add start and end date columns to their spreadsheets. As such, Excel includes several functions that tell you how many days pass between two separate dates. DATEDIF, DAYS360, DATE, and NETWORKDAYS are four functions that you can add to cells that will tell you the number of days between two dates.

This is how you find the number of days between dates with and without these functions in Excel.

How to find difference between dates without function

First, you can find the difference between dates by subtracting them. Excel doesn’t have a subtract function, but you can still add subtraction formulas to cells.

  • So, open a blank excel spreadsheet and enter the start and end date in cells B4 and C4 as shown in the snapshot below. Note that the dates must be in US format: first the month, then the day, and then the year.
  • Now select cell D4, click inside the formula bar at the top and type “=C4-B4” in the row and press Enter. Cell D4 will return the value 34.

Finding the difference between two dates using the DATE function

Also, you can find the difference between two dates using the DATE function. You can then find the number of days by entering the dates into the function bar instead of spreadsheet cells. The basic syntax for this function is: =DATE(yyyy,m,d)-DATE(yyyy,m,d); so you enter the dates in reverse order.

Let’s add this function with the same dates 04/01/2017 and 05/05/2017.

  • Select a cell in the spreadsheet to add a function. Then click inside the function bar, type “=DATE(2022, 5, 5)-DATE(2022, 4, 1)” and press Enter. You may also need to select a general number format for the cell if the returned value is in date format. In general number format, the cell will return 34 days as shown in the screenshot below.

Finding the difference between two dates using the DATEDIF function

DATEDIF is a flexible function that allows you to calculate the total number of days between dates by entering the dates in a spreadsheet or function panel. However, DATEDIF is not listed in Excel’s “Insert Function” window.

Thus, you will need to enter it directly into the function panel. Syntax of the RAZDAT function: RAZDAT(start_date, end_date, unit of measure). You can enter a start date and an end date or cell references for specific dates in a function, and then add units of days at the end.

  • To find the difference between the two dates entered in cells C4 and B4, enter the following on the function line: ‘=DIV(B4, C4, “d)”. The DATEDIF cell will include the number of days between dates, as shown directly below.

However, this is much more flexible than the DATE function since you can change the units. For example, suppose you want to count the number of days between two dates, but ignore years.

  • You can do this by adding “yd” to the function. For example, enter “04/01/2022” and “05/05/2023” into two cells, and then include “yd” in the function as shown below.

This returns a value of 34 days between 04/01/2022 and 05/05/2023, which is correct if you ignore the year. If the function did not ignore the year, the value would be 399.

Finding the difference between two dates using the DAYS360 function

The DAYS360 function finds the total number of days between dates based on the 360-day calendar, which is more commonly used for fiscal years. So this might be the best feature for account spreadsheets. This won’t make much of a difference for dates only a few months apart, but for longer periods DAYS360 will return slightly different values ​​than the other functions.

  • Enter “01/01/2021” and “01/01/2022” in cells B6 and C6 of the spreadsheet.
  • Then click the cell where you want to include the DAYS360 function, click Formulas > Date and Time.
  • From the Date and Time drop-down menu, select DAYS360.
  • Press the Start_date button and enter “B6”, press the End_date button and enter “C6”, and then press OK.
  • The DAYS360 function will return the value 360.

Finding the difference between two dates using the NETWORKDAYS function

What if you need to find the difference between two dates but exclude weekends from the equation? DATEDIF, DATE and DAYS360 are not very suitable for this scenario. NETWORKDAYS is a function that finds the number of days between dates, excluding weekends, and can also take into account additional holidays, such as public holidays.

So this should be a feature for project planning. The basic syntax of the function is: =NETWORKDAYS(start_date, end_date, [holidays]).

  • Click the cell where you want to add the function and click Formulas > Date and Time > NETWORKDAYS.
  • Enter “B7” for Start_date and “C7” for End_date and click OK.
  • Using start and end dates of 04/01/2022 and 05/05/2022, the NETWORKDAYS function returns a value of 25 days between dates without holidays. Including weekends, the total number of days is 34, as in the previous examples.
  • To include additional holidays in the function, enter these dates in additional cells in the spreadsheet. Then, click the Holidays cell reference button in the NETWORKDAYS function window and select the cell or cells that contain holiday dates. This will subtract the holidays from the final figure.

Thus, there are many ways to calculate days between start and end dates in Excel spreadsheets. Later versions of Excel also include a DAYS function that you can use to find the difference between a pair of dates. These functions will certainly come in handy for spreadsheets containing a large number of dates.

Frequently asked Questions

What does #NUMBER mean?

When you run the above formulas and get #NUMBER and not a number, it’s because the start date is greater than the end date. Try rearranging the dates and doing the steps again.

Leave a Reply

Your email address will not be published.