How much time has passed?

  • Thread starter Thread starter BK
  • Start date Start date
B

BK

Using Excel 2003

I am using the following formula to calculate how much time has passed since
a particular event.

=DATEDIF(D2,NOW(),"y")

This returns the passage of time in full years. I am stumped trying to get
the result in years and months such as "3 years 6 months." Can anyone help?
 
=DATEDIF(D2,TODAY(),"y")&" years "&DATEDIF(D2,TODAY(),"YM")&" months"

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
For months use:

=DATEDIF(D2,TODAY(),"ym")

Returns the number of odd months left over after the number of years. It
can be combined into one cell as in:

=DATEDIF(D2,TODAY(),"y")&" years "&DATEDIF(D2,TODAY(),"ym")&" Months"

If you want to avoid it saying " 1 Months" instead of " 1 Month" then
use:

=DATEDIF(D2,TODAY(),"y")&" year &"&IF(DATEDIF(D2,TODAY(),"y")<>1,"s ","
")&DATEDIF(D2,TODAY(),"ym")&" Month"&IF(DATEDIF(D2,TODAY(),"ym")<>1,"s ","")


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks so much. This does the job.



Bob Phillips said:
=DATEDIF(D2,TODAY(),"y")&" years "&DATEDIF(D2,TODAY(),"YM")&" months"

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
 
Thanks so much. This does the job.



Sandy Mann said:
For months use:

=DATEDIF(D2,TODAY(),"ym")

Returns the number of odd months left over after the number of years. It
can be combined into one cell as in:

=DATEDIF(D2,TODAY(),"y")&" years "&DATEDIF(D2,TODAY(),"ym")&" Months"

If you want to avoid it saying " 1 Months" instead of " 1 Month" then
use:

=DATEDIF(D2,TODAY(),"y")&" year &"&IF(DATEDIF(D2,TODAY(),"y")<>1,"s ","
")&DATEDIF(D2,TODAY(),"ym")&" Month"&IF(DATEDIF(D2,TODAY(),"ym")<>1,"s
","")


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
You're very welcome. Thanks for the feedback'

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top