Date Difference

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
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.
 

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,760
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Aug 13, 2015
Messages
186

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFG
1
2
31/1/20213/30/202102292 Months 29 Days
41/2/20212/3/2023225125 Months 1 Day
51/3/20215/2/2025451294 Years 3 Months 29 Days
61/4/20218/18/202107147 Months 14 Days
71/5/20211/31/2021002626 Days
81/6/20211/6/202433603 Years
91/7/20215/7/20210404 Months
101/8/20212/8/20210101 Month
111/9/20211/15/202433663 Years 6 Days
Sheet832
Cell Formulas
RangeFormula
D3:D11D3=DATEDIF(B3,C3,"y")
E3:E11E3=DATEDIF(B3,C3,"m")
F3:F11F3=DAYS(C3,EDATE(B3,E3))
G3:G11G3=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.
 
Last edited:
Solution

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186

ADVERTISEMENT

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

Book3.xlsx
ABCDEFG
1
2
31/1/20213/30/202102292 Months 29 Days
41/2/20212/3/2023225125 Months 1 Day
51/3/20215/2/2025451294 Years 3 Months 29 Days
61/4/20218/18/202107147 Months 14 Days
71/5/20211/31/2021002626 Days
81/6/20211/6/202433603 Years
91/7/20215/7/20210404 Months
101/8/20212/8/20210101 Month
111/9/20211/15/202433663 Years 6 Days
Sheet832
Cell Formulas
RangeFormula
D3:D11D3=DATEDIF(B3,C3,"y")
E3:E11E3=DATEDIF(B3,C3,"m")
F3:F11F3=DAYS(C3,EDATE(B3,E3))
G3:G11G3=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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABCH
1
2
31/1/20213/30/20212 Months 29 Days
41/2/20212/3/202325 Months 1 Day
51/3/20215/2/20254 Years 3 Months 29 Days
61/4/20218/18/20217 Months 14 Days
71/5/20211/31/202126 Days
81/6/20211/6/20243 Years
91/7/20215/7/20214 Months
101/8/20212/8/20211 Month
111/9/20211/15/20243 Years 6 Days
Sheet832
Cell Formulas
RangeFormula
H3:H11H3=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

Well-known Member
Joined
Feb 15, 2002
Messages
4,760
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What did you try?

Datedif.xlsm
ABCD
1
21-Jan-211-Jan-2212 months
31-Jan-211-Jul-216 months
41-Jan-211-Feb-2325 months
51-Jan-2110-Jan-2324 months 9 days
61-Jan-212-Feb-309 years 1 month 1 day
71-Jan-2118-Aug-3110 years 7 months 17 days
1c
Cell Formulas
RangeFormula
D2:D7D2=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
BCD
21-Jan-211-Jan-221 year, 0 months, 0 days
31-Jan-211-Jul-210 years, 6 months, 0 days
41-Jan-211-Feb-232 years, 1 month, 0 days
51-Jan-2110-Jan-232 years, 0 months, 9 days
61-Jan-212-Feb-309 years, 1 month, 1 day
71-Jan-2118-Aug-3110 years, 7 months, 17 days
1c
Cell Formulas
RangeFormula
D2:D7D2=ymd(B2,C2)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,703
Messages
5,654,825
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top