DAYS Function: A Brief
DAYS function in Excel is used to calculate the number of days between two dates. If you have a start date in cell A1 and an end date in cell B1, the formula =DAYS(B1, A1) will return the total number of days between those two dates.
If your system uses the mm/dd/yy date format but your formula uses a different format like dd/mm/yyyy, Excel may show a #VALUE! error. This happens because the date format in the formula doesn’t match your system’s date setting, causing Excel to misread the date.
|
Objective |
Value Returned by function |
|---|---|
|
Aim to return days between two dates in number |
DAYS Function will extract & return the number of days between the Ending date and the Starting date. |
DAYS Function: A Syntax
=DAYS(end_date, start_date)
- end_date: The later date.
- start_date: The earlier date.
Note: The result will be a positive or negative number depending on the order of dates.
Function Compatibility:
The DAYS function was introduced in Excel 2013. It is supported in all modern versions of Excel, including Excel 2013, 2016, 2019, and Microsoft 365. If you are using older versions of Excel, then you might need to use the formula: =DATEDIF(start_date, end_date, "d").
Basic Example:
|
S.no |
End Date |
Start Date |
Final Result |
Formula Used |
Comments |
|---|---|---|---|---|---|
|
1 |
12-Mar-24 |
01-Jan-24 |
71 |
|
Includes Feb 29, so leap year is considered |
|
2 |
05-Jan-23 |
25-Dec-22 |
11 |
|
Days between December and January, different years |
|
3 |
19-Jun-21 |
01-Aug-21 |
-43 |
|
Reversed dates, returns a negative number |
|
4 |
03-Oct-20 |
05-Sep-20 |
28 |
|
In 2020, a leap year, but Feb not in range |
|
5 |
08-May-23 |
01-Jan-23 |
127 |
|
Days between two dates in the same year |
|
6 |
30-Jun-22 |
15-Jun-22 |
15 |
|
Days between two dates in the same month and year |
- The 1st explains that the DAYS function automatically checks whether the given dates include a leap year. If so, it adds one extra day for the month of February.
- The 2nd shows that the DAYS function can calculate the difference in days between dates that fall in different years.
- The 3rd explains that if the end date is earlier than the start date, the DAYS function will return a negative value.
- The 4th shows that even if a leap year is present, the DAYS function will ignore it if February is not within the range of the dates.
- The 5th explains that the DAYS function can find the difference between two dates in the same year.
- The 6th shows that the DAYS function can also calculate the difference between two dates within the same month.
In the above example, I’ve tried to cover all the necessary possibilities of using DAYS function in Excel.
Using Other Functions Nested with DAYS Function:
|
Start Date |
End Date |
Today’s Date |
|---|---|---|
|
01 January 2023 |
30 June 2024 |
01-Apr-25 |
In the below example, I’ve listed the available combinations of using DAYS functions with other Excel functions.
|
Label |
Formula Used |
|---|---|
|
Days Between |
=DAYS(B7,B4) |
|
Days Remaining |
=DAYS(B7,B10) |
|
Status (Overdue) |
=IF(DAYS(B10,B7)<0,”Overdue”,”On Time”) |
|
Duration Text |
=TEXT(DAYS(B7,B4),”0″) & ” days between start and end” |
|
Subscription Status |
=IF(DAYS(B10,B4)>365,”Expired”,”Active”) |
|
Months Duration |
=DATEDIF(B4,B7,”m”) |
|
Fancy Duration |
=DATEDIF(B4,B7,”y”) & ” years, ” & DATEDIF(B4,B7,”ym”) & ” months, ” & DATEDIF(B4,B7,”md”) & ” days” |
That’s it. This article is originally published on How to Use Excel DAYS Function?