Use Datedif but for future dates

[bam]

I have a formula for identifying years, months, days from a past date to
now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " &
DATEDIF(C6,NOW(),"md") & " D"

I'd like a formula that can produce the same format (years, months, days)
between now and a future date.

Any ideas?

Thanks in advance,

Bart

 

[Ron de Bruin]

Hi Bart

Try this with the date in A2

=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&DATEDIF(A2,TODAY(),"y")& " y "
&DATEDIF(A2,TODAY(),"ym")& " m")

 

[Ron de Bruin]

Oops, This is the correct one for YMD

=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m " & DATEDIF(TODAY(),A2,"md") & "
d","-"&DATEDIF(A2,TODAY(),"y") & " y " &DATEDIF(A2,TODAY(),"ym") & " m " &DATEDIF(A2,TODAY(),"md") & " d ")

 

[bam]

Wow, nice. That does it. Thanks very much
Bart

Oops, This is the correct one for YMD

=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"y") & " y " & DATEDIF(TODAY(),A2,"ym")
& " m " & DATEDIF(TODAY(),A2,"md") & " d","-"&DATEDIF(A2,TODAY(),"y") & "
y " &DATEDIF(A2,TODAY(),"ym") & " m " &DATEDIF(A2,TODAY(),"md") & " d ")

 

[Gord Dibben]

Swap your references around.

DATEDIF(NOW(),C6, etc.

Earliest date must be first.


Gord Dibben MS Excel MVP