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


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
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!


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

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

Guy --

What about using:


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?



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

Here's my attempt...