Date Difference

kparadise · Mar 4, 2021

Hello.

I need to find the Years, Months, and Days between two dates.

Caveat (1); if those values equal zero, I do not want them to display, so I came up with the below which is working great.

Code:

=IF(DATEDIF(W3,Z3,"y"),DATEDIF(W3,Z3,"y")&" YEARS, ","") &
IF(DATEDIF(W3,Z3,"ym"), DATEDIF(W3, Z3, "ym") &" MONTHS, ", "") &
IF(DATEDIF(W3,Z3,"md"), DATEDIF(W3, Z3, "md") &" DAYS", "")

Caveat (2); this is were I need help. I only need the YEARS description once the difference hits 36 months. If the difference is less than 36 months, I do not want the YEARS; I would just need the value in Months and Days. Once it hits 36 Months, then I want the YEARS/MONTHS/DAYS value.

i.e.
11 MONTHS, 20 DAYS
9, YEARS, 10 MONTHS, 13 DAYS
18 MONTHS
17 MONTHS, 16 DAYS
33 MONTHS, 1 DAYS
3 YEARS, 6 DAYS
4 YEARS, 1 MONTHS, 4 DAYS

Caveat (3); Is there a way to use the plural form of MONTH(S) and DAY(S) only when necessary? If it is greater than 1, use the plural.

Misca · Mar 4, 2021

It's just adding IF formulas one after another so try something along these lines:

Excel Formula:

=IF(DATEDIF(B3,C3,"m")>36,
DATEDIF(B3,C3,"y")&" years",
IF(DATEDIF(B3,C3,"m"),
DATEDIF(B3,C3,"m")&" month"&
IF(DATEDIF(B3,C3,"m")>1,"s",""),"")&
IF(DATEDIF(B3,C3,"md"),
IF(DATEDIF(B3,C3,"m"),", ","")&
DATEDIF(B3,C3,"md")&" day"&
IF(DATEDIF(B3,C3,"md")>1,"s",""),""))

kparadise · Mar 4, 2021

Misca said:
It's just adding IF formulas one after another so try something along these lines:

Excel Formula:

=IF(DATEDIF(B3,C3,"m")>36, DATEDIF(B3,C3,"y")&" years", IF(DATEDIF(B3,C3,"m"), DATEDIF(B3,C3,"m")&" month"& IF(DATEDIF(B3,C3,"m")>1,"s",""),"")& IF(DATEDIF(B3,C3,"md"), IF(DATEDIF(B3,C3,"m"),", ","")& DATEDIF(B3,C3,"md")&" day"& IF(DATEDIF(B3,C3,"md")>1,"s",""),""))

This is defiantly close; but when using this formula; when the months are greater than 36, it is not providing the MONTH and DAY vaules... it is just showing the YEAR value. i.e. It should be 9 YEARS, 1 MONTH, 25 DAYS..... but this formula only gives: 9 YEARS.

kparadise · Mar 4, 2021

This is defiantly close; but when using this formula; when the months are greater than 36, it is not providing the MONTH and DAY vaules... it is just showing the YEAR value. i.e. It should be 9 YEARS, 1 MONTH, 25 DAYS..... but this formula only gives: 9 YEARS.

Dave Patton · Mar 4, 2021

Your profile does not show the version of Excel that you are using.
Please edit your profile.
If you have 365, review and edit the following.
Once you have the result that you require, you can combine the two suggestions.

Let_a.xlsm

A

B

C

D

1

2

3

1-Jan-21

30-Jun-21

5 months,29 days

4

1-Jan-21

2-Feb-23

25 months,1 day

5

1-Jan-21

2-Feb-30

9 years, 1 month,1 day

6

1-Jan-21

18-Aug-30

9 years, 7 months, 17 days

7

2a

Cell Formulas
Range	Formula
D3:D4	D3	=LET(Mo,DATEDIF(B3,C3,"m"),Da,DATEDIF(B3,C3,"md"),IF(Mo<36,IF(Mo,Mo&" month"&IF(Mo>1,"s,",""))&Da&" day"&IF(Da>1,"s","")))
D5:D6	D5	=LET(Yr,DATEDIF(B5,C5,"y"),Ym,DATEDIF(B5,C5,"ym"),Da,DATEDIF(B5,C5,"md"),Yr&" years, "&IF(Ym,Ym&" month"&IF(Ym>1,"s, ",",")&IF(Da,Da&" day"&IF(Da>1,"s",""))))

kparadise · Mar 5, 2021

Dave Patton said:
Your profile does not show the version of Excel that you are using.
Please edit your profile.
If you have 365, review and edit the following.
Once you have the result that you require, you can combine the two suggestions.

Let_a.xlsm
A B C D
1
2
3 1-Jan-21 30-Jun-21 5 months,29 days
4 1-Jan-21 2-Feb-23 25 months,1 day
5 1-Jan-21 2-Feb-30 9 years, 1 month,1 day
6 1-Jan-21 18-Aug-30 9 years, 7 months, 17 days
7
2a
Cell Formulas
Range Formula
D3:D4 D3 =LET(Mo,DATEDIF(B3,C3,"m"),Da,DATEDIF(B3,C3,"md"),IF(Mo<36,IF(Mo,Mo&" month"&IF(Mo>1,"s,",""))&Da&" day"&IF(Da>1,"s","")))
D5:D6 D5 =LET(Yr,DATEDIF(B5,C5,"y"),Ym,DATEDIF(B5,C5,"ym"),Da,DATEDIF(B5,C5,"md"),Yr&" years, "&IF(Ym,Ym&" month"&IF(Ym>1,"s, ",",")&IF(Da,Da&" day"&IF(Da>1,"s",""))))

So I would need to use two different formulas? Is there a way to combine them into one?

Dave Patton · Mar 5, 2021

"Once you have the result that you require, you can combine the two suggestions."

You can use if < 36 months formula, else formula

Dave Patton · Mar 5, 2021

I included examples below. I would probably just use a UDF.

Let_a.xlsm

B

C

D

E

F

3

1-Jan-21

1-Jan-22

12 months,

12 months

4

1-Jan-21

2-Feb-23

25 months,1 day

25 months 1 day

5

1-Jan-21

2-Feb-30

9 years, 1 month 1 day

9 years 1 month 1 day

6

1-Jan-21

18-Aug-30

9 years, 7 months,17 days

9 years 7 months 17 days

2a

Cell Formulas
Range	Formula
D3:D6	D3	=LET(Yr,DATEDIF(B3,C3,"y"),Ym,DATEDIF(B3,C3,"ym"),Da,DATEDIF(B3,C3,"md"),IF(OR(B3=C3,C3<B3),"",IF(Yr<3,"",Yr)&IF(Yr>=3," years, ","")&IF(Ym+(Yr<3)Yr12,Ym+(Yr<3)Yr12&" month")&IF(Ym+(Yr<3)*12>1,"s,", " ")&IF(Da,Da&" day","")&IF(Da>1,"s","")))
E3:E6	E3	=LET(Yr,DATEDIF(B3,C3,"y"),Ym,DATEDIF(B3,C3,"ym"),Da,DATEDIF(B3,C3,"md"),IF(OR(B3=C3,C3<B3),"",IF(Yr<3,"",Yr)&IF(Yr>=3," years ","")&IF(Ym+(Yr<3)Yr12,Ym+(Yr<3)Yr12&" month","")&IF(Ym+(Yr<3)*12>1,"s ", " ")&IF(Da,Da&" day","")&IF(Da>1,"s","")))
F3:F6	F3	=IF(DATEDIF(B3,C3,"y")<3,LET(Mo,DATEDIF(B3,C3,"m"),Da,DATEDIF(B3,C3,"md"),IF(Mo,Mo&" month"&IF(Mo>1,"s ",""))&IF(Da,Da&" day","")&IF(Da>1,"s","")),LET(Yr,DATEDIF(B3,C3,"y"),Ym,DATEDIF(B3,C3,"ym"),Da,DATEDIF(B3,C3,"md"),Yr&" years "&IF(Ym,Ym&" month"&IF(Ym>1,"s "," ")&IF(Da,Da&" day"&IF(Da>1,"s","")))))

kparadise · Mar 8, 2021

Dave Patton said:
I included examples below. I would probably just use a UDF.

Let_a.xlsm
B C D E F
3 1-Jan-21 1-Jan-22 12 months, 12 months 12 months
4 1-Jan-21 2-Feb-23 25 months,1 day 25 months 1 day 25 months 1 day
5 1-Jan-21 2-Feb-30 9 years, 1 month 1 day 9 years 1 month 1 day 9 years 1 month 1 day
6 1-Jan-21 18-Aug-30 9 years, 7 months,17 days 9 years 7 months 17 days 9 years 7 months 17 days
2a
Cell Formulas
Range Formula
D3:D6 D3 =LET(Yr,DATEDIF(B3,C3,"y"),Ym,DATEDIF(B3,C3,"ym"),Da,DATEDIF(B3,C3,"md"),IF(OR(B3=C3,C3<B3),"",IF(Yr<3,"",Yr)&IF(Yr>=3," years, ","")&IF(Ym+(Yr<3)*Yr*12,Ym+(Yr<3)*Yr*12&" month")&IF(Ym+(Yr<3)*12>1,"s,", " ")&IF(Da,Da&" day","")&IF(Da>1,"s","")))
E3:E6 E3 =LET(Yr,DATEDIF(B3,C3,"y"),Ym,DATEDIF(B3,C3,"ym"),Da,DATEDIF(B3,C3,"md"),IF(OR(B3=C3,C3<B3),"",IF(Yr<3,"",Yr)&IF(Yr>=3," years ","")&IF(Ym+(Yr<3)*Yr*12,Ym+(Yr<3)*Yr*12&" month","")&IF(Ym+(Yr<3)*12>1,"s ", " ")&IF(Da,Da&" day","")&IF(Da>1,"s","")))
F3:F6 F3 =IF(DATEDIF(B3,C3,"y")<3,LET(Mo,DATEDIF(B3,C3,"m"),Da,DATEDIF(B3,C3,"md"),IF(Mo,Mo&" month"&IF(Mo>1,"s ",""))&IF(Da,Da&" day","")&IF(Da>1,"s","")),LET(Yr,DATEDIF(B3,C3,"y"),Ym,DATEDIF(B3,C3,"ym"),Da,DATEDIF(B3,C3,"md"),Yr&" years "&IF(Ym,Ym&" month"&IF(Ym>1,"s "," ")&IF(Da,Da&" day"&IF(Da>1,"s","")))))

This is not working, I am getting a NAME error.

,LET(Mo,DATEDIF(B3,C3,"m")

That first section is causing the error.

Dave Patton · Mar 8, 2021

Quote from post #5
Your profile does not show the version of Excel that you are using.
Please edit your profile.
If you have 365, review and edit the following.
Once you have the result that you require, you can combine the two suggestions.

Do you have Excel 365 and the Let function?
Did the previous suggestions work for you?
Did the other examples work for you?

If you have the Let function, click on the icon below the F(x) in my post with the suggestions and post into
a new clean sheet. Then review the formulas.

Date Difference

kparadise

Board Regular

Excel Facts

Misca

Well-known Member

kparadise

Board Regular

kparadise

Board Regular

Dave Patton

Well-known Member

kparadise

Board Regular

Dave Patton

Well-known Member

Dave Patton

Well-known Member

kparadise

Board Regular

Dave Patton

Well-known Member

Similar threads

Forum statistics

Share this page

Date Difference

Board Regular

Excel Facts

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock