Calculate duration (#yrs., # mos.)

C

CherylC

Sorry if this has already been posted/answered; but I'm not having any luck
finding it!

What I need to do is calculate the duration of a condition in years and
months. i.e. Start = 6/1/01; End = 9/30/03 would return 2 yrs., 4 mos.

To complicate matters further, certain conditions are necessary in rtegards
to the number of months. If the remaining number of days total between 0 and
14, it should round down to the lesser whole month; between 15 and 28, I need
the number of months to return a ".5" at the end; between 29 and 30, it
should round up to the next whole month.

Therefore,
Start = 6/1/01; End = 9/9/03 would return "2 yrs., 3 mos."
Start = 6/1/01; End = 9/19/03 would return "2 yrs., 3.5 mos."
Start = 6/1/01; End = 9/29/03 would return "2 yrs., 4 mos."


Hope I explained this correctly!
I'm using Excel 2003.

Thanks in advance for any help!!!
 
M

Mike H

Hi,

With the earlier date in a1 try this

=DATEDIF(A1,B1,"y")&" yrs
"&IF(DATEDIF(A1,B1,"md")<15,DATEDIF(A1,B1,"ym"),IF(DATEDIF(A1,B1,"md")<29,DATEDIF(A1,B1,"ym")+0.5,DATEDIF(A1,B1,"ym")+1))&" Mths"

Mike
 
C

Cheryl

By george, I think you've got it - thanks so much! My head was spinning
trying to figure that out!!!
 

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