Datedif not working HELP

[SamBT]

i am using this formula on MS Teams and it is giving me an answer of 1 Yr 11mnths instead of 2 yrs 0 months can any one tell me why - the dates is 01/04/2020 to 31/01/2022
=IF(AND(ISBLANK(DV89); ISBLANK(DW89)); ""; IF(OR(ISBLANK(DV89); ISBLANK(DW89)); "PERIOD UNKNOWN"; IF(OR(ISERROR(DV89); ISERROR(DW89)); "FIX DATE"; DATEDIF(DV89; DW89; "Y") + IF(DAY(DW89) >= DAY(DV89); 0; -1) & " YRS " & MOD(DATEDIF(DV89; DW89; "M"); 12) + IF(DAY(DW89) >= DAY(DV89); 0; 1) & " MTHS")))

 

[Alex Blakenburg]

The example you gave actually generates 1 yr 9 mths not 1 yr 11 mths. Its a question of interpretation. DateDif is using the concept of Anniversary date. The year does not click over until the anniversary date so in your case the anniversary date is 01/04/2022 not 31/03/2022. If you want to do what you seem to be expecting just subtract 1 day from the start date or add 1 day to the end date (make sure you do the same to both the Year and the Month calculations)
PS: for the months you should have been able to use DATEDIF(DV89; DW89; "YM")

 

[SamBT]

Hi thank you is there perhaps another formula I can use I added +1 into the formula and it is still giving me the same answer 1 YRS 11 MTHS if I add +2 it changes to 1 YRS 0 MNTHS

 

[Alex Blakenburg]

I am using your formula and that is not what I am getting:

20241129 DateDif Years and Months SamBT.xlsx
	A	B	C																																																																																																																										DU	DV	DW
88	Original Formula	=DATEDIF(DV89,DW89,"Y")	=DATEDIF(DV89,DW89,"YM")																																																																																																																											Start	End
89	1 YRS 9 MTHS	1	9																																																																																																																											1/04/2020	31/01/2022
90	2 YRS 0 MTHS	2	0																																																																																																																											1/04/2020	1/04/2022
Sheet1

Cell Formulas
Range	Formula
B88:C88	B88	=FORMULATEXT(B89)
A89:A90	A89	=IF(AND(ISBLANK(DV89), ISBLANK(DW89)), "", IF(OR(ISBLANK(DV89), ISBLANK(DW89)), "PERIOD UNKNOWN", IF(OR(ISERROR(DV89), ISERROR(DW89)), "FIX DATE", DATEDIF(DV89, DW89, "Y") + IF(DAY(DW89) >= DAY(DV89), 0, -1) & " YRS " & MOD(DATEDIF(DV89, DW89, "M"), 12) + IF(DAY(DW89) >= DAY(DV89), 0, 1) & " MTHS")))
B89:B90	B89	=DATEDIF(DV89,DW89,"Y")
C89:C90	C89	=DATEDIF(DV89,DW89,"YM")

 

[Alex Blakenburg]

This has an alternative with the additional day factored in:

20241129 DateDif Years and Months SamBT.xlsx
	A	B	C																																																																																																																										DU	DV	DW
88	Original Formula	Alternative formula (with additional day added)																																																																																																																												Start	End
89	1 YRS 9 MTHS	1 YRS 10 MTHS																																																																																																																												1/04/2020	31/01/2022
90	2 YRS 0 MTHS	2 YRS 0 MTHS																																																																																																																												1/04/2020	1/04/2022
91	1 YRS 11 MTHS	2 YRS 0 MTHS																																																																																																																												1/04/2020	31/03/2022
Sheet1

Cell Formulas
Range	Formula
A89:A91	A89	=IF(AND(ISBLANK(DV89), ISBLANK(DW89)), "", IF(OR(ISBLANK(DV89), ISBLANK(DW89)), "PERIOD UNKNOWN", IF(OR(ISERROR(DV89), ISERROR(DW89)), "FIX DATE", DATEDIF(DV89, DW89, "Y") + IF(DAY(DW89) >= DAY(DV89), 0, -1) & " YRS " & MOD(DATEDIF(DV89, DW89, "M"), 12) + IF(DAY(DW89) >= DAY(DV89), 0, 1) & " MTHS")))
B89:B91	B89	=IF(AND(ISBLANK(DV89), ISBLANK(DW89)), "", IF(OR(ISBLANK(DV89), ISBLANK(DW89)), "PERIOD UNKNOWN", IF(OR(ISERROR(DV89), ISERROR(DW89)), "FIX DATE", DATEDIF(DV89, DW89+1, "Y") & " YRS " & DATEDIF(DV89, DW89+1, "YM") & " MTHS")))

 

[SamBT]

You are an absolute genius. Thank you so much for helping me the +1 helped. Thank you very much.

 

[Alex Blakenburg]

You're welcome. Glad I could help.