Find months and days between todays date and another date that is the result of a function?

[ScottWilliams324]

I have tried to do this for the last hour or so and it's kicking my butt. I'm in Excel 2010, and the cell with the date I want to work from is H22, can anyone help me please?


I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.


Here's the latest thing I tried (that doesn't work):


=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))


I should also probably note that the date in H22 is the result of another function.


=EDATE(G22,12)

 

[BlueHornet]

Hi Scott, and welcome to the board!

Have you tried the DateDif() function? With that you can get the whole number of completed months between two dates. Unfortunately, it doesn't return fractional months, so we'd have to fake something for that.

That function works as
=DateDif( [older date], [more recent date], "m"), and if you combine that with a quick way to count the "missing" days, then ...

=DATEDIF( TODAY(), H22, "m") & " months and " & DAY( TODAY()) - DAY( H22) & IF( DAY( TODAY()) - DAY( H22) = 1, " day", " days")

How does that work for you?

PS: You can also count days ("d") and years ("y"), depending on the granularity of the date difference you want to capture.

 

[ScottWilliams324]

Hi Scott, and welcome to the board!

Have you tried the DateDif() function? With that you can get the whole number of completed months between two dates. Unfortunately, it doesn't return fractional months, so we'd have to fake something for that.

That function works as
=DateDif( [older date], [more recent date], "m"), and if you combine that with a quick way to count the "missing" days, then ...

=DATEDIF( TODAY(), H22, "m") & " months and " & DAY( TODAY()) - DAY( H22) & IF( DAY( TODAY()) - DAY( H22) = 1, " day", " days")

How does that work for you?

PS: You can also count days ("d") and years ("y"), depending on the granularity of the date difference you want to capture.

That worked perfectly! Thank you so much! You are awesome!

 

[BlueHornet]

Oh, gosh. "Perfection" and "awesome" were much higher than I was aiming for; I would have been happy with "workable" and "helpful". You're welcome, and thanks for the feedback.

 

[shg]

Wow -- you sure about that?

[Table="width:, class:grid"][tr][td] [/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

21​

[/td][td]

04/26/2014​

[/td][td]H21: =TODAY()[/td][/tr]

[tr][td]

22​

[/td][td]

05/01/2014​

[/td][td]H22: Input[/td][/tr]

[tr][td]

23​

[/td][td]0 months and 25 days[/td][td]H23: =DATEDIF(H21, H22, "m") & " months and " & DAY(H21) - DAY(H22) & IF( DAY(H21) - DAY( H22) = 1, " day", " days")[/td][/tr]
[/table]

 

[BlueHornet]

Good catch, shg. It was even worse than that. If I put a date that gave us an H22 date a couple of days higher than today, then we could get "1 month and -1 days", which was also short of perfection.

So, maybe this, although it hasn't been checked exhaustively:
=IF( DATEDIF( TODAY(), H22, "m") < 1, "0 months and " & DATEDIF( TODAY(), H22, "d") & IF( DATEDIF( TODAY(), H22, "d") = 1, " day", " days"), DATEDIF( TODAY(), H22, "m") & IF( DATEDIF( TODAY(), H22, "m") = 1, " month", " months") & " and " & ABS( DAY( TODAY()) - DAY( H22)) & IF( ABS( DAY( TODAY()) - DAY( H22)) = 1, " day", " days"))

EDIT: It should also be noted that a date too far in the past, which results in an H22 date in the past (when compared to TODAY()) will result in a #NUM! error, since the DATEDIF() function requires the dates to be put in the correct chronological sequence.