Sometimes, you may need to calculate the number of days between two dates in Excel, such as the duration of a project, the age of a person, the elapsed time since an event, etc. In this article, we will show you how to do this using different methods and formulas. We will also explain the basic theory behind the calculations and provide a detailed example with real data.
Excel stores dates as serial numbers, which represent the number of days that have passed since January 1, 1900. For example, the date January 1, 2021 is stored as 44197, which means 44,197 days have passed since January 1, 1900. This makes it easy to perform arithmetic operations on dates, such as adding or subtracting days.
To calculate the number of days between two dates, we simply need to subtract the smaller date from the larger date. For example, to calculate the number of days between January 1, 2021 and January 31, 2021, we can use the formula:
=January 31, 2021 – January 1, 2021
This will return 30, which is the correct answer.
However, there are some caveats and variations that we need to consider when using this method, such as:
- The result may include fractional days, depending on the time values of the dates. For example, if the start date is January 1, 2021 12:00 PM and the end date is January 2, 2021 6:00 AM, the result will be 0.75, which means 0.75 days or 18 hours have passed between the two dates. To get the number of whole days, we need to round down the result using the FLOOR function, such as:
=FLOOR(January 2, 2021 6:00 AM – January 1, 2021 12:00 PM, 1)
This will return 0, which means 0 days have passed between the two dates.
- The result may be negative, if the end date is earlier than the start date. For example, if the start date is January 31, 2021 and the end date is January 1, 2021, the result will be -30, which means -30 days have passed between the two dates. To get the absolute value of the result, we need to use the ABS function, such as:
=ABS(January 1, 2021 – January 31, 2021)
This will return 30, which means 30 days have passed between the two dates, regardless of the order.
- The result may not be accurate, if the dates are in different calendar systems, such as the Gregorian calendar and the Julian calendar. Excel uses the 1900 date system, which assumes that the year 1900 was a leap year, even though it was not. This causes a discrepancy of one day between Excel dates and actual dates for dates before March 1, 1900. To correct this, we need to add 1 to the result, if the dates are before March 1, 1900, such as:
=January 1, 1800 – January 1, 1700 + 1
This will return 36525, which is the correct answer.
Procedures
To calculate the number of days between two dates in Excel, we can follow these steps:
- Enter the start date and the end date in two separate cells, such as A1 and A2. Make sure the cells are formatted as dates, using the Format Cells dialog box or the Home tab on the ribbon.
- In another cell, such as B1, enter the formula to subtract the start date from the end date, such as:
=A2-A1
- Adjust the formula according to the caveats and variations mentioned above, such as rounding down, taking the absolute value, or adding 1, if necessary.
- Press Enter to get the result. The result will be the number of days between the two dates.
Example
Let’s say we want to calculate the number of days between June 1, 2020 and May 31, 2021, which are the start and end dates of a fiscal year. We can use the following table to enter the data and the formula:
| Start Date | End Date | Formula | Result |
|---|---|---|---|
| 6/1/2020 | 5/31/2021 | =B2-A2 | 365 |
The result is 365, which means 365 days have passed between the two dates.
Other Approaches
Besides the basic method of subtracting dates, there are other approaches that we can use to calculate the number of days between two dates in Excel, such as:
- Using the DATEDIF function, which returns the number of days, months, or years between two dates, based on a specified interval. For example, to calculate the number of days between June 1, 2020 and May 31, 2021, we can use the formula:
=DATEDIF(6/1/2020, 5/31/2021, “d”)
This will return 365, which is the same as the previous method. The “d” argument specifies that we want the number of days as the interval. We can also use other arguments, such as “m” for months, “y” for years, “ym” for months excluding years, “yd” for days excluding years, or “md” for days excluding months and years.
- Using the NETWORKDAYS function, which returns the number of working days between two dates, excluding weekends and holidays. For example, to calculate the number of working days between June 1, 2020 and May 31, 2021, assuming that the weekends are Saturday and Sunday, and there are no holidays, we can use the formula:
=NETWORKDAYS(6/1/2020, 5/31/2021)
This will return 261, which means 261 working days have passed between the two dates. We can also specify a range of cells that contain the dates of holidays, and add it as the third argument of the function, such as:
=NETWORKDAYS(6/1/2020, 5/31/2021, C2:C10)
This will exclude the dates in C2:C10 from the calculation, and return a different result, depending on the number and dates of holidays.
- Using the DAYS function, which returns the number of days between two dates, similar to the basic method of subtracting dates. For example, to calculate the number of days between June 1, 2020 and May 31, 2021, we can use the formula:
=DAYS(5/31/2021, 6/1/2020)
This will return 365, which is the same as the previous methods. The only difference is that the order of the arguments is reversed, with the end date as the first argument and the start date as the second argument.
