sia.hackernoon.com

DAYS Function in Excel Returns the Difference in Days between Two Dates, ignoring time!

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	=DAYS(B4,C4)	Includes Feb 29, so leap year is considered
2	05-Jan-23	25-Dec-22	11	=DAYS(B5,C5)	Days between December and January, different years
3	19-Jun-21	01-Aug-21	-43	=DAYS(B6,C7)	Reversed dates, returns a negative number
4	03-Oct-20	05-Sep-20	28	=DAYS(B8,C8)	In 2020, a leap year, but Feb not in range
5	08-May-23	01-Jan-23	127	=DAYS(B9,C9)	Days between two dates in the same year
6	30-Jun-22	15-Jun-22	15	=DAYS(B10,C10)	Days between two dates in the same month and year

From the above example,

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:

In the below example, I’ve listed the available combinations of using DAYS functions with other Excel functions.

Label	Formula Used	Output	Explanation
Days Between	=DAYS(B7,B4)	546	How many days from start to end
Days Remaining	=DAYS(B7,B10)	-275	Days left from today until end
Status (Overdue)	=IF(DAYS(B10,B7)<0,"Overdue","On Time")	On Time	Checks if task is overdue
Duration Text	=TEXT(DAYS(B7,B4),"0") & " days between start and end"	546 days between start and end	Human-friendly sentence showing total days
Subscription Status	=IF(DAYS(B10,B4)>365,"Expired","Active")	Expired	Active or Expired based on 365 days check
Months Duration	=DATEDIF(B4,B7,"m")	17	Calculates full months between dates
Fancy Duration	=DATEDIF(B4,B7,"y") & " years, " & DATEDIF(B4,B7,"ym") & " months, " & DATEDIF(B4,B7,"md") & " days"	1 years, 5 months, 29 days	Shows months and leftover days