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?

 

[snjpverma]

Try if this works:

=DATEDIF(G2,TODAY(),"y")&" Years "&DATEDIF(G2,TODAY(),"ym")&" Months"

 

[janema]

Last one. Promise.

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

Thanks so much for your help! For some reason, I keep getting an error on them. I ended up putting the date and locking that and it worked using "two" dates.

 

[Alex Blakenburg]

Assuming you want completed months I don't believe that (post # 10) version of the formula will give you a correct result.
What happened when you tried @snjpverma's post # 11 version ?

PS: I seem to be in the minority but I am not a fan of putting Today or Now directly into a formula.
a) it is a volatile function and constantly recalculating
b) is is being used in the formula like you would a constant and which lends it to being put into a cell like a parameter.
This makes testing easier since you only have one place you to change it and helps rerun a report when you discover you need to run the report retropectively because you missed running one.

20230222 DateDif Years and Months janema.xlsx
	A	B	C	D	E	F	G
1	DateDif	Post #10					Hire Date
2	2 Years 0 Months	2 Years 2Months					24/01/2021
3
4
5
6	Current Date --->	22/02/2023
7
8	DateDif	Post #10					Hire Date
9	2 Years 0 Months	2 Years 2Months					24/01/2021
Data

Cell Formulas
Range	Formula
A2	A2	=DATEDIF(G2,TODAY(),"y")&" Years "&DATEDIF(G2,TODAY(),"ym")&" Months"
B2	B2	=IF(MONTH(TODAY())>MONTH(G2),YEAR(TODAY())-YEAR(G2),YEAR(TODAY())-(YEAR(G2))-1)&" Years "&MONTH(TODAY())&"Months"
B6	B6	=TODAY()
A9	A9	=DATEDIF(G9,$B$6,"y")&" Years "&DATEDIF(G9,$B$6,"ym")&" Months"
B9	B9	=IF(MONTH($B$6)>MONTH(G9),YEAR($B$6)-YEAR(G9),YEAR($B$6)-(YEAR(G9))-1)&" Years "&MONTH($B$6)&"Months"