Formula to calculate elapsed dates.

[Cerberus]

I am trying to create a formula to calculate how many years and months an
employee has worked for us. In cell K2 we have the date of the report
(12/31/08), in cell G17 we have the hire date (5/6/02). Now in cell I17 we
want the years of service (6) and in cell J17 we want the months (8).

I tried to do something like =K2-G17 and then Ctrl+Shift+~ to get 2431 and
then I did Ans/365.25 to get the number of years (6.655715) but now I can't
figure out how to get to drop the .655715 and use the remainder to multiply
by 12 to get the months.

If you have any ideas on how to do that or if any of you have a better way
to go about get the proper results I would greatly appreciate it.

Thanks in advance.

 

[Cerberus]

Sorry for the repost. When I posted this I received an error so I did not
think it went the first time.

 

[Ron Rosenfeld]

I am trying to create a formula to calculate how many years and months an
employee has worked for us. In cell K2 we have the date of the report
(12/31/08), in cell G17 we have the hire date (5/6/02). Now in cell I17 we
want the years of service (6) and in cell J17 we want the months (8).

I tried to do something like =K2-G17 and then Ctrl+Shift+~ to get 2431 and
then I did Ans/365.25 to get the number of years (6.655715) but now I can't
figure out how to get to drop the .655715 and use the remainder to multiply
by 12 to get the months.

If you have any ideas on how to do that or if any of you have a better way
to go about get the proper results I would greatly appreciate it.

Thanks in advance.

Hard to compute exactly since both months and years can have differing number
of days. But here's one method:

I17: =DATEDIF(G17,K2,"y")
J17: =DATEDIF(G17,K2,"ym")

Documentation for DATEDIF is not present unless you are using Excel 2000. See:

http://www.cpearson.com/excel/datedif.aspx
--ron