MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Age in quarters!!! - Any Help Very Much Appreciated


Posted by Guy on December 09, 2001 10:02 AM

Hi,

I've been trying to solve this problem for ages now. Its probably really easy but its been driving me insane for 3 months now!!!!

Basically i have a spread sheet where you put in a date of birth and a date this person wishes to retire. I want excel to then say this person is aged 24 years and 0.75 quarters...

I've got the age part ok but i then take the modulous and compare it with less than greater than 0 to 0.25, 0.25 to 0.5 and 0.5 to 0.75 it all goes wrong.

The problem is when we have
DOB=3/7/77
Ret Date=30/6/01
After taking number of says and / 365 it gives : 24.00821. This modulous .00821 then fits into the range 0 to 0.25 and so its no quarters! It should be .75 since they are almost at their birthday!!!

I did a check for negative numbers so when the retirement date is less than the month in the DOB it takes the modulous away from 1 and gets 0.991 - it works for this example but not for others!

i.e
DOB=3/7/77
Ret=3/6/01

Answer = 23.934 (i.e. this time the modulous is correct but doing a negative check will make it come out with the wrong number).

Any solutions? In principle it sounds really easy, i.e. Work out the difference between 2 dates in quarters but it just so hard!

If anyone dosent really understand what i'm asking i'd be happy to emial you it (tiny <20k).

Thanks for any help
Guy


Posted by Aladin Akyurek on December 09, 2001 11:02 AM

Guy --

What about using:

=DATEDIF(A1,A2,"Y")+DATEDIF(A1,A2,"ym")/12

where A1=DOB and A2=RET. The format of the cell of the formula is set to 2 decimals. Applied to your sample, we get 23.92.

Does this meet your needs?

Aladin

======

Posted by Mark W. on December 10, 2001 11:08 AM

Here's my attempt...

ERR