Use Datedif but for future dates

B

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
 
R

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")
 
R

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 ")
 
B

bam

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

Ron de Bruin said:
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 ")
 
G

Gord Dibben

Swap your references around.

DATEDIF(NOW(),C6, etc.

Earliest date must be first.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top