| Date and Time functions | Detailed Site Index | Display numbers as words |

Calculate an age between two dates using the DATEDIF function

It is easy to determine the number of days between any two dates simply by subtracting one from the other, but it is extremely difficult to return a string that says something like '24 years, 6 months and 8 days'. The maths and logic involved in this apparently straightforward question are very complex. You may wish to try it using the MONTH() and DAY() functions, but make sure you thoroughly test your formula!

An Excel function called DATEDIF() will perform this calculation although it is not included within the Excel help files or the function wizard until Excel 2000. It does however exist in previous versions of Excel. It takes the following structure: =DATEDIF(Date1, Date2, OutputRequirement). The first two parameters are simple, although you must ensure that the second date is greater than the first. The OutputRequirement parameter is a character code (typed inside inverted commas) that specifies the type of value you wish to obtain.

Code Description Explanation
"d" Days The number of days between Date1 and Date2.
"m" Months The number of complete months between Date1 and Date2.
"y" Years The number of complete years between Date1 and Date2.
"yd" Days excluding Years The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.
"ym" Months excluding Years The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.
"md" Days excluding Months and Years The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

This function is ideally suited to calculating someones precise age on any particular date or the number of days until your next birthday. The first date can be your date of birth and the second date is either entered in another cell by the user, or automatically set to todays date with the NOW() function.

- A B C
23   First Date Second Date
24 Date as typed 02/02/84 06/06/07
25 Date formatted 02 Feb 84 06 Jun 07
26 DateDif using 'D' argument   8525
27 DateDif using 'YD' argument   125
28 DateDif using 'MD' argument   4

[C26] =DATEDIF(B24, C24, "D")
[C27] =DATEDIF(B24, C24, "YD")
[C28] =DATEDIF(B24, C24, "MD")

The diagram (right) illustrates the difference between the different types of day count. The first formula (row 26) counts the total number of days between the 2 dates. The next formula counts the days which are the remainder after the whole years are deducted. The final formula returns the number days after both the whole years and the months have been deducted.

- A B C D
30   First Date Second Date  
31 Date as typed 02/05/97 06/06/07  
32 Date formatted 02 May 97 06 Jun 07  
33 Years 10  [B33] =DATEDIF(B31, C31, "Y")
34 Months 1  [B34] =DATEDIF(B31, C31, "YM")
35 Days 4  [B35] =DATEDIF(B31, C31, "MD")

- A
38 Your age is 10 years, 1 months and 4 days.
39 [A38] ="Your age is " & TEXT(B33, "0") & " years, " &
TEXT(B34, "0") & " months and " & TEXT(B35, "0") & " days."

The second diagram (left) shows how the function can be incorporated within a complex formula to return a sentence describing a time interval (in cell A38). The TEXT() function is the correct way of incorporating both numeric values within a string of characters.

General information about Date and Time functions is also available within this site.

It is possible to convert whole phrases, including number values, into proper words (e.g. 'Twelve years and Four days'). This requires a user defined function such as is shown here.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xl_birthday.htm 2016 Last updated Feb14 CMIDX S4 P3 Y