Introduction
The Excel Days function is a Date/Time Function. It tells the count of the number of days between a start date and the end date.
The Excel Datedif function is also a Date/Time Function. It returns the count of the number of days, months, or years between two given dates.
Excel Days Function
Let there be a starting date A1 and an ending date E1.
=DAYS(E1, A1) will return the number of days between the two dates.
The purpose of using this function is to get the days between two dates.
The Return Value is a number representing the days.
SYNTAX -
“=DAYS(endDate, startDate)”
ARGUMENTS -
startDate(the starting date) and endDate(the ending date).
Both the start and end dates must be valid Excel dates. They can also be text values that can be parsed as dates. They should be whole numbers and not fractional values.
The Days function will return a negative value if we reverse the start and end dates.
Therefore, =DAYS(E1, A1) = E1-A1
EXAMPLES
-
=DAYS("15-Jun-21", "1-Jun-21")
//the function will return 14. -
If we want to include both the starting and ending date in the count, increase the count by 1.
=DAYS("2-Jun-21", "1-Jun-21")+1
//this will return 2
Points to Note
- We should avoid storing or parsing date represented by text values because it might produce errors. Thus it is better to work with native Excel dates.
- The Excel Days function works only with whole numbers.
- If the Days function can't recognize any date, it will return #VALUE! error.
- If the dates are out of range, it will return #NUM! error.
Excel Datedif Function
The Excel Datedif function returns the count of the number of days, months, or years between two given dates.
The purpose of this function is to the number of days, months, or years between two dates.
The Return Value is a number representing the time between two dates.
SYNTAX -
“=DATEDIF(startDate, endDate, unit)”
ARGUMENTS -
startDate(the starting date), endDate(the ending date), and the unit is the time unit that we want to use, and it is supplied as text.
DATEDIF = DATE+IF.
It is a compatibility function and comes from Lotus 1-2-3.
Time Units
Unit | Result |
"y" | Difference in complete years |
"m" | Difference in complete months |
"d" | Difference in days |
"md" | Difference in days, ignoring months and years |
"ym" | Difference in months, ignoring years |
"yd" | Difference in days, ignoring years |
Examples
-
=DATEDIF("1-Jan-18", "1-Mar-20", "y")
//Returns 2 -
=DATEDIF("1-Jan-18", "1-Mar-20", "m")
//Returns 26 -
=DATEDIF("1-Jan-18", "1-Mar-20", "d")
//Returns 790 -
=DATEDIF("1-Jun-15", "15-Sep-21", "my")
//Returns 3 as it is completely ignoring the years and just counting the months. -
=DATEDIF("1-Jun-15", "15-Sep-21", "md")
//Returns 14 as it is completely ignoring the months and years. -
=DATEDIF(A1, TODAY(), “y”)
//This will return the current age of a person in years.
Points to Note
- Excel doesn't help us to find the DATEDIF function.
- If the startDate is greater than the endDate, the function throws a #NUM error.
- Microsoft recommends us not to use the "md" value for the time unit as it may give a negative result or a zero, or an inaccurate result.
FAQs
-
What is the return value of the DAYS function?
Number -
What type of number does the DAYS function work with?
Whole Numbers. -
What should we avoid while using the DAYS function?
We should prevent parsing the dates represented by text values as it may produce errors. -
What is the return value of the DATEDIF function?
Number -
What happens if startDate is greater than endDate while using the Datedif Function?
It will throw a #NUM error.