Formula to calculate tenure (Years & Months)

[janema]

=DATEDIF(G2,NOW(),”Y”)& ”years", “&DATEDIF(G2,TODAY(),”YM”)& ”months“

I am using the above formula to calculate the tenure date in years and months for people, but it keeps coming back as #NAME? error. G2 in above formula is the date a person started at the organization. I am trying to just calculate between that date and today/current without having to use two separate dates, if that's possible. I tried the "NOW" and "TODAY" but both return that #NAME? error. Any idea what I'm doing wrong here?

 

[Skyybot]

Are the cells containing the date data formatted as date? Would you be able to provide a visual of your worksheet?

 

[janema]

Are the cells containing the date data formatted as date? Would you be able to provide a visual of your worksheet?

Yes, the date data is formatted as a date. Sure, please see here:

and here is the data in G2

 

[Skyybot]

Try this

=YEAR(TODAY())-YEAR(G2) & " Years " & MONTH(G2) - MONTH(TODAY()) & " Months"

 

[Skyybot]

Try this

=YEAR(TODAY())-YEAR(G2) & " Years " & MONTH(G2) - MONTH(TODAY()) & " Months"

I just realized that will only work if TODAY month is less than DATE month. You may have nest an IF in there to get the larger month.

 

[Skyybot]

I just realized that will only work if TODAY month is less than DATE month. You may have nest an IF in there to get the larger month.

=YEAR(TODAY())-YEAR(G2) & " Years " & IF(MONTH(TODAY())>MONTH(G2),MONTH(TODAY())-MONTH(G2),MONTH(G2) - MONTH(TODAY())) & " Months"

 

[janema]

I might be better off using two dates and having a field with today's date it sounds?

 

[Skyybot]

=YEAR(TODAY())-YEAR(G2) & " Years " & IF(MONTH(TODAY())>MONTH(G2),MONTH(TODAY())-MONTH(G2),MONTH(G2) - MONTH(TODAY())) & " Months"

That really doesn't work either. Another IF to subtract 1 year if DATE month is greater than TODAY month

 

[Skyybot]

=DATEDIF(G2,NOW(),”Y”)& ”years", “&DATEDIF(G2,TODAY(),”YM”)& ”months“

I am using the above formula to calculate the tenure date in years and months for people, but it keeps coming back as #NAME? error. G2 in above formula is the date a person started at the organization. I am trying to just calculate between that date and today/current without having to use two separate dates, if that's possible. I tried the "NOW" and "TODAY" but both return that #NAME? error. Any idea what I'm doing wrong here?

I think I have it.

=IF(MONTH(TODAY())<MONTH(G2),YEAR(TODAY())-YEAR(G2),YEAR(TODAY())-YEAR(G2))-1&" Years "&MONTH(TODAY())&"Months"

 

[Skyybot]

Last one. Promise.

=IF(MONTH(TODAY())>MONTH(G2),YEAR(TODAY())-YEAR(G2),YEAR(TODAY())-(YEAR(G2))-1)&" Years "&MONTH(TODAY())&"Months"