DATEDIF
The DATEDIF function returns the number of days, months or years between two dates.
DATEDIF(start-date, end-date, calc-method)
start-date: The starting date. start-date is a date/time value (the time portion is ignored) or date string value.
end-date: The ending date. end-date is a date/time value (the time portion is ignored) or date string value.
calc-method: A modal value that specifies how to express the time difference and how dates in different years or months are handled.
“D”: Return the number of days between the start and end dates.
“M”: Return the number of months between the start and end dates.
“Y”: Return the number of years between the start and end dates.
“MD”: Return the days between the start and end dates, ignoring months and years. The month in end-date is considered to be the month in start-date. If the starting day is after the ending day, the count starts from the ending day as if it were in the preceding month. The year of the end-date is used to check for a leap year.
“YM”: Return the number of whole months between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.
“YD”: Return the number of days between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.
Examples |
|---|
If A1 contains the date/time value 6/4/1988 and A2 contains the date/time value 30/10/2006: =DATEDIF(A1,A2,“D”) returns 6,781, the number of days between 6 April 1988 and 30 October 2006. =DATEDIF(A1,A2,“M”) returns 222, the number of whole months between 6 April 1988 and 30 October 2006. =DATEDIF(A1,A2,“Y”) returns 18, the number of whole years between 6 April 1988 and 30 October 2006. =DATEDIF(A1,A2,“MD”) returns 24, the number of days between the sixth day of a month and the thirtieth day of the same month. =DATEDIF(A1,A2,“YM”) returns 6, the number of months between April and the following October in any year. =DATEDIF(A1,A2,“YD”) returns 207, the number of days between 6 April and the following 30 October in any year. =DATEDIF(“06/04/1988”,NOW(),“Y”)& “ years, ” &DATEDIF(“06/04/1988”,NOW(),“YM”)& “ months and ” &DATEDIF(“06/04/1988”,NOW(),“MD”)& “ days” returns 25 years, 2 months and 25 days, the current age of someone born on 6 April 1988 if today is 1 July 2013. |