Greater than the result of a DateDif

[weerder]

I am using DateDif to come up with a result such as 1 year 4 months or 5 years 11 months. I want to have an adjacent column, possibly using IF but not necessarily, that will give me a result of One, if more than one year, Five, if more than five years etc. Can this be done? TIA

 

[Dave Patton]

"if more than one year, Five, if more than five years etc. "
What if the result is exactly 1 year or exactly 5 years?
It is probably easier to calculate from the original information.

Dates Time.xlsm
	A	B	C	D	E	F	G	H
2	Start	End
3	1-Jan-22	5-May-23	1 Years and 4 Months	1			1.0001	1
4	1-Jan-22	5-May-23	1.339726027	1			5.0001	5
5	1-Jan-22	5-May-27		5
4f

Cell Formulas
Range	Formula
C3	C3	=DATEDIF(A3,B3,"y")&" Years and "&DATEDIF(A3,B3,"ym")& " Months"
C4	C4	=YEARFRAC(A4,B4,1)
D3	D3	=LOOKUP((B3-A3)/365,G3:H4)
D4	D4	=LOOKUP(C4,G3:H4)
D5	D5	=LOOKUP(YEARFRAC(A5,B5,1),G4:H5)

 

[weerder]

"if more than one year, Five, if more than five years etc. "
What if the result is exactly 1 year or exactly 5 years?
It is probably easier to calculate from the original information.

Dates Time.xlsm
	A	B	C	D	E	F	G	H
2	Start	End
3	1-Jan-22	5-May-23	1 Years and 4 Months	1			1.0001	1
4	1-Jan-22	5-May-23	1.339726027	1			5.0001	5
5	1-Jan-22	5-May-27		5
4f

Cell Formulas
Range	Formula
C3	C3	=DATEDIF(A3,B3,"y")&" Years and "&DATEDIF(A3,B3,"ym")& " Months"
C4	C4	=YEARFRAC(A4,B4,1)
D3	D3	=LOOKUP((B3-A3)/365,G3:H4)
D4	D4	=LOOKUP(C4,G3:H4)
D5	D5	=LOOKUP(YEARFRAC(A5,B5,1),G4:H5)

Thanks for this Dave. It is not critical whether it is exactly 1 or 5 years so I have not bothered. What I am after is a text value in the adjacent cell if the value is greater than.

 

[Dave Patton]

Did you try the suggested alternatives?
What result do you expect?
Please post a few examples and the result that you expect.
What "Text Value" do you expect for 5.0 Years?
What "Text Value" do you expect for 5 Years 6 months?
What "Text Value" do you expect for 7.5 years?

Dates Time.xlsm
	A	B	C	D	E	F	G
1
2	Start	End
3	1-Jan-22	5-May-23		ABC		1	ABC
4	1-Jan-22	5-May-27		XYZ		5	XYZ
5	01-01-22	5-Jan-25		ABC
6
4f

Cell Formulas
Range	Formula
D3	D3	=VLOOKUP((B3-A3)/365,$F$3:$G$4,2,1)
D4	D4	=VLOOKUP((B4-A4)/365,F3:G4,2,1)
D5	D5	=VLOOKUP((B5-A5)/365,$F$3:$G$3,2,1)