Calculate difference between dates showing years months days or year month day for singular units

[Phil Smith]

Hi everyone. I am trying to work out the difference between two dates and then display the result as years, months & days BUT if the result for each unit is 1, how to display the answer as the singular year month & day. Apologies for my brain fog not allowing me to work it out.

 

[6StringJazzer]

Please:

1. Give an example of what you want the results to look like
2. Give your Excel version, as the answer can vary for older vs. newer versions (I suggest updating your profile to include this)

 

[Phil Smith]

Please:

1. Give an example of what you want the results to look like
2. Give your Excel version, as the answer can vary for older vs. newer versions (I suggest updating your profile to include this)

Thanks Jeff. Will do that now! I havent been on here for a while. I am using Office 365, currently on an iPad as I am without my PC.

The desired result would be, for example, “10 years 3 months and 2 days” for multiple units, or “ 1 year 1 month 1 day”

 

[6StringJazzer]

If the number is 0, do you still want to see it?
10 years and 2 days

 

[Phil Smith]

If the number is 0, do you still want to see it?
10 years and 2 days

Excellent question! I hadn’t thought of that option. I think if the relevant unit is 0 then it would be best not to display it.

 

[Dave Patton]

Please try and test the following.

Datedif.xlsm
	A	B	C	D
1		01-01-21	30-03-21	2 months 29 days
1d

Cell Formulas
Range	Formula
D1	D1	=LET(y,DATEDIF(B1,C1,"y"),m,DATEDIF(B1,C1,"ym"),d,DATEDIF(B1,C1,"md"),IF(y>0,y&" year"&IF(y>1,"s "," "),"")&IF(m>0,DATEDIF(B1,C1,"ym")&" month"&IF(m>1,"s "," "),"")&IF(DATEDIF(B1,C1,"md"),d&" day"&IF(d>1,"s",""),""))

 

[Phil Smith]

Please try and test the following.

Datedif.xlsm
	A	B	C	D
1		01-01-21	30-03-21	2 months 29 days
1d

Cell Formulas
Range	Formula
D1	D1	=LET(y,DATEDIF(B1,C1,"y"),m,DATEDIF(B1,C1,"ym"),d,DATEDIF(B1,C1,"md"),IF(y>0,y&" year"&IF(y>1,"s "," "),"")&IF(m>0,DATEDIF(B1,C1,"ym")&" month"&IF(m>1,"s "," "),"")&IF(DATEDIF(B1,C1,"md"),d&" day"&IF(d>1,"s",""),""))

Sorry for the delay in getting back to you Dave. Wow, that is one hell of a formula! Thank you so much for your hard work in solving the problem and working out a formula to deliver EXACTLY what I requested. Much appreciated!

 

[Dave Patton]

Thanks for the feedback.

 

[stangeba]

Thanks! I used it to calculate the age of my Grandfathers 1937 Dodge Trk when I first drove it in 1969 vs. the age of MY 1971 Monte Carlo when my Grandson will drive it this Thanksgiving 2023!