Calulate the number of years and months from a specific date

**hodkd** · 01-22-2009, 03:15 PM

I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

**NBVC** · 01-22-2009, 03:26 PM

So what exactly would the format of the answer be?

given 2 dates... say June 1, 2007 and August 8, 2008 for example

**hodkd** · 01-22-2009, 03:47 PM

I am trying to determine when someone has reached 5, 10, 15, etc years injury free years.

A B C
no. yrs/mts reached 5, 10
Date Hired worked 15, etc years

10-01-1969 39 yrs 1 month will show 5
today years in Oct

**NBVC** · 01-22-2009, 03:50 PM

huh? Don't get it.

**hodkd** · 01-22-2009, 04:32 PM

I need to calculate the number of years and months, shown by a number, since a certain date. Like how many years and months someone has worked since they were hired.

**NBVC** · 01-22-2009, 05:18 PM

So do you mean?

=DATEDIF(A5,TODAY(),"y")&" years and "&DATEDIF(A5,TODAY(),"ym")&" months"

where A5 contains Start Date

**hodkd** · 01-22-2009, 05:24 PM

Thanks, that works, but I ment to say I need to show the months as a descimal.

**NBVC** · 01-22-2009, 05:28 PM

You mean?

=DATEDIF(A5,TODAY(),"y")&" years and "&ROUND(DATEDIF(A5,TODAY(),"ym")/12,2)&" months"

or

=(TODAY()-A5)/12 (formatted as General) for total months between dates

**hodkd** · 01-22-2009, 05:51 PM

Thanks, I put it in years with descimal by deviding by 365 instead of 12. The next part I needed was to show if the number of years equals 5, 10, 15, 20, 25, 30 40, etc, years to indicate that in another column and if it doesn't equal one of those numbers show a N in the column

**NBVC** · 01-22-2009, 06:01 PM

Maybe:

=If(MOD(INT((TODAY()-A5)/365),5)=0,"Y","N")

This will take the Year portion (or Integer portion) and check to see if it is exactly equally divisible by 5 (i.e. =5,10,15,20, etc.). If it is, then "Y" appears, else an "N" appears.