Chatting about Excel and More

Calculating Age or Years of service


When you are calculating age or years of service, you can't just subtract the date from the current date. You will not get the correct answer if you do that. Instead, you need to use =yearfrac() which will give you a fraction based on the number of days between the Start Date and the End Date.

The syntax is =yearfrac(today(),A1,1))
My brother's birthday is next week so let's see how old he actually is:

38.97 sounds better than 39 doesn't it?  Now, if you wanted it as a whole number you could simply use the INT() function with it.
=INT((YEARFRAC(TODAY(),A1,1)))

Now- be careful - if you use the INT, his age would display as 38. After all, he is not 39 yet. Don't rush him. Just wait until next year. Egad. my baby brother is getting old.
 ========================================================

For those of you who remember Lotus, you can also use DATEDIF(). This function provides you with a little more flexibility.
DATEDIF() is not documented in Excel and has to be typed in manually.
Rumor has it that it was a very popular Lotus function and back when people were transitioning there was a huge outcry over its loss and Microsoft added it in - grudgingly..ergo.. no documentation.

DATEDIF() allows you to display the date in terms of years, months or days.
The syntax is DATEDIF(start date, end date, time interval)                                                                         
Time intervals include:
"Y" The number of complete years
"M" The number of complete months
"D" The number of days.
"MD" The difference between the days in start_date and end_date.
          The months & years of the dates are ignored.

"YM" The difference between the months in start_date and end_date.

          The days & years of the dates are ignored.

"YD" The difference between the days of start_date and end_date.

          The years of the dates are ignored.

If you are really into it, you can join these functions all together using & and quotes

and get something like this:
=DATEDIF(A1,NOW(),"Y")&" years" &" "&DATEDIF(A1,NOW(),"ym") &" months "
and the resulting answer would display as 38 years 11 months

Remember, the & joins items together and you need quotes around text. I left a space before and after the word years and months so that everything would not run together. If you don't press your space bar to get the spaces, your answer will look like this: 38years11months instead of 38 years 11 months.