Date Difference

[kparadise]

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.

=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.

 

[kparadise]

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.

I have Excel 2013.

I am just looking for one magic formula to use, but none of the above formulas are working for me. Some of these worked, but did not give the appropriate outputs.

 

[jtakw]

I have Excel 2013.

I am just looking for one magic formula to use, but none of the above formulas are working for me. Some of these worked, but did not give the appropriate outputs.

Hi,

Are you open to Helper Columns?

 

[Dave Patton]

1. You could eliminate the Let function; the formula will be longer. Just replace each part of the Let such as Yr with the Datedif that it represents.

2. Use a User Defined Function. There is a UDF on Rick Rothstein's site. You would have to customize it for the features that you requested.

3. You could use separate cells for each type of datedif calculation and then reference these cells in a formula.

 

[kparadise]

Hi,

Are you open to Helper Columns?

At this point in time, I am open for anything.

 

[kparadise]

1. You could eliminate the Let function; the formula will be longer. Just replace each part of the Let such as Yr with the Datedif that it represents.

2. Use a User Defined Function. There is a UDF on Rick Rothstein's site. You would have to customize it for the features that you requested.

3. You could use separate cells for each type of datedif calculation and then reference these cells in a formula.

1. I am not sure what you mean, this is the reason I am asking for help.
2. Never used UDF.
3. I rather one massive formula if possible.

 

[jtakw]

Hi,

Are you open to Helper Columns?

At this point in time, I am open for anything.

With 3 Helper Columns, Hide these columns if you don't want them showing, this formula should give you what you want:

Book3.xlsx
	A	B	C	D	E	F	G
1
2
3		1/1/2021	3/30/2021	0	2	29	2 Months 29 Days
4		1/2/2021	2/3/2023	2	25	1	25 Months 1 Day
5		1/3/2021	5/2/2025	4	51	29	4 Years 3 Months 29 Days
6		1/4/2021	8/18/2021	0	7	14	7 Months 14 Days
7		1/5/2021	1/31/2021	0	0	26	26 Days
8		1/6/2021	1/6/2024	3	36	0	3 Years
9		1/7/2021	5/7/2021	0	4	0	4 Months
10		1/8/2021	2/8/2021	0	1	0	1 Month
11		1/9/2021	1/15/2024	3	36	6	3 Years 6 Days
Sheet832

Cell Formulas
Range	Formula
D3:D11	D3	=DATEDIF(B3,C3,"y")
E3:E11	E3	=DATEDIF(B3,C3,"m")
F3:F11	F3	=DAYS(C3,EDATE(B3,E3))
G3:G11	G3	=TRIM(IF(D3>=3,D3&" Years "&IF(MOD(E3,12)>0,MOD(E3,12)&" Month"&IF(MOD(E3,12)>1,"s "," ")," ")&IF(F3>0,F3&" Day"&IF(F3>1,"s",""),""),IF(E3>0,E3&" Month"&IF(E3>1,"s "," ")&IF(F3>0,F3&" Day"&IF(F3>1,"s",""),""),F3&" Day"&IF(F3>1,"s",""))))

Edit: Updated formula shorter.

 

[kparadise]

With 3 Helper Columns, Hide these columns if you don't want them showing, this formula should give you what you want:

Book3.xlsx
	A	B	C	D	E	F	G
1
2
3		1/1/2021	3/30/2021	0	2	29	2 Months 29 Days
4		1/2/2021	2/3/2023	2	25	1	25 Months 1 Day
5		1/3/2021	5/2/2025	4	51	29	4 Years 3 Months 29 Days
6		1/4/2021	8/18/2021	0	7	14	7 Months 14 Days
7		1/5/2021	1/31/2021	0	0	26	26 Days
8		1/6/2021	1/6/2024	3	36	0	3 Years
9		1/7/2021	5/7/2021	0	4	0	4 Months
10		1/8/2021	2/8/2021	0	1	0	1 Month
11		1/9/2021	1/15/2024	3	36	6	3 Years 6 Days
Sheet832

Cell Formulas
Range	Formula
D3:D11	D3	=DATEDIF(B3,C3,"y")
E3:E11	E3	=DATEDIF(B3,C3,"m")
F3:F11	F3	=DAYS(C3,EDATE(B3,E3))
G3:G11	G3	=TRIM(IF(D3>=3,D3&" Years "&IF(MOD(E3,12)>0,MOD(E3,12)&" Month"&IF(MOD(E3,12)>1,"s "," ")," ")&IF(F3>0,F3&" Day"&IF(F3>1,"s",""),""),IF(E3>0,E3&" Month"&IF(E3>1,"s "," ")&IF(F3>0,F3&" Day"&IF(F3>1,"s",""),""),F3&" Day"&IF(F3>1,"s",""))))

Edit: Updated formula shorter.

Perfect thank you! I just included those helper formulas into the big one, so it is all one formula.

 

[jtakw]

Perfect thank you! I just included those helper formulas into the big one, so it is all one formula.

You're welcome, thanks for the feedback, glad you finally got it working.
If you forgo the helper columns and incorporated the helper formulas into the result formula, you probably have something like this:

Book3.xlsx
	A	B	C					H
1
2
3		1/1/2021	3/30/2021					2 Months 29 Days
4		1/2/2021	2/3/2023					25 Months 1 Day
5		1/3/2021	5/2/2025					4 Years 3 Months 29 Days
6		1/4/2021	8/18/2021					7 Months 14 Days
7		1/5/2021	1/31/2021					26 Days
8		1/6/2021	1/6/2024					3 Years
9		1/7/2021	5/7/2021					4 Months
10		1/8/2021	2/8/2021					1 Month
11		1/9/2021	1/15/2024					3 Years 6 Days
Sheet832

Cell Formulas
Range	Formula
H3:H11	H3	=TRIM(IF(DATEDIF(B3,C3,"y")>=3,DATEDIF(B3,C3,"y")&" Years "&IF(MOD(DATEDIF(B3,C3,"m"),12)>0,MOD(DATEDIF(B3,C3,"m"),12)&" Month"&IF(MOD(DATEDIF(B3,C3,"m"),12)>1,"s "," ")," ")&IF(DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))>0,DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))&" Day"&IF(DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))>1,"s",""),""),IF(DATEDIF(B3,C3,"m")>0,DATEDIF(B3,C3,"m")&" Month"&IF(DATEDIF(B3,C3,"m")>1,"s "," ")&IF(DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))>0,DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))&" Day"&IF(DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))>1,"s",""),""),DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))&" Day"&IF(DAYS(C3,EDATE(B3,DATEDIF(B3,C3,"m")))>1,"s",""))))

 

[Dave Patton]

What did you try?

Datedif.xlsm
	A	B	C	D
1
2		1-Jan-21	1-Jan-22	12 months
3		1-Jan-21	1-Jul-21	6 months
4		1-Jan-21	1-Feb-23	25 months
5		1-Jan-21	10-Jan-23	24 months 9 days
6		1-Jan-21	2-Feb-30	9 years 1 month 1 day
7		1-Jan-21	18-Aug-31	10 years 7 months 17 days
1c

Cell Formulas
Range	Formula
D2:D7	D2	=IF(DATEDIF(B2,C2,"y")<3,IF(DATEDIF(B2,C2,"m"),DATEDIF(B2,C2,"m")&" month"&IF(DATEDIF(B2,C2,"m")>1,"s ",""),"")&IF(DATEDIF(B2,C2,"md"),DATEDIF(B2,C2,"md")&" day","")&IF(DATEDIF(B2,C2,"md")>1,"s",""),DATEDIF(B2,C2,"y")&" years "&IF(DATEDIF(B2,C2,"ym")>0,DATEDIF(B2,C2,"ym")&" month"&IF(DATEDIF(B2,C2,"ym")>1,"s "," "),"")&IF(DATEDIF(B2,C2,"md"),DATEDIF(B2,C2,"md")&" day"&IF(DATEDIF(B2,C2,"md")>1,"s",""),""))

UDF

Datedif.xlsm
	B	C	D
2	1-Jan-21	1-Jan-22	1 year, 0 months, 0 days
3	1-Jan-21	1-Jul-21	0 years, 6 months, 0 days
4	1-Jan-21	1-Feb-23	2 years, 1 month, 0 days
5	1-Jan-21	10-Jan-23	2 years, 0 months, 9 days
6	1-Jan-21	2-Feb-30	9 years, 1 month, 1 day
7	1-Jan-21	18-Aug-31	10 years, 7 months, 17 days
1c

Cell Formulas
Range	Formula
D2:D7	D2	=ymd(B2,C2)