# Thread: Round Down a date value (Excel 2003 )

1. ## Round Down a date value (Excel 2003 )

Why doesn't this formula work? =rounddown(TODAY()-I23)/365.25 I need it to round an age down. For example, if a person's birth date is 12/19/1974, the formula without "rounddown" will calculate an age of 31.87. I want it to have a value of 31.

2. ## Re: Round Down a date value (Excel 2003 )

You can also use
<code>
=DATEDIF(I23,TODAY(),"y")
</code>

3. ## Re: Round Down a date value (Excel 2003 )

<P ID="edit" class=small>(Edited by 79schultz on 01-Nov-06 11:11. Ran into another snag.)</P>I believe I figured it out. Here's what I did.

[b]=ROUNDDOWN((TODAY()-I14)/365.25,0)[b]

One other problem I ran into though is that if a child has not yet reached their first birthday, it will show a value of '0'. Is there a way in code or something to have it calculate how many months old the child is?

4. ## Re: Round Down a date value (Excel 2003 )

Try:

<code>
=IF(DATEDIF(I23,TODAY(),"y")>0,DATEDIF(I23,TODAY() ,"y")&" years",DATEDIF(I23,TODAY(),"m")&" Months")
</code>

5. ## Re: Round Down a date value (Excel 2003 )

Thanks a lot! That's exactly what I needed!

6. ## Re: Round Down a date value (Excel 2003 )

Cor blimey Legare

don't get me started on trying to show someone's current age with Excel.
I did some work for a hospital once - well more like a baby factory really (according to Excel they produced over two tons of baby per annum) - ok some people might call it a hospital with a speciality maternity unit.
Anyway, ..so you get a hospital ID assigned to all patients (including babies) and of course you have date of birth and need age etc.
Simple I thought - Excel no problem!
..So for most of us it was OK - we just really want age shown in years.
For kids, you use different measurements - e.g. mothers will say theirs is "4 months old" or maybe "6 weeks" or "3 days" (anyone born today would be say "five hours old" or maybe just referred to as "newborn".
Up to the teenage years, children might say "I'm 6 years and 7 months"

The worst problem of course were the lucky persons born in 1898 etc.
Never mind the "year 2000" problem, this was the classic "year 1900" problem.
(Excel not being able to simply handle dates before 01-Jan-1900)

Good luck in creating a generic formula to handle the lot.

(I had to use a custom function in the end)

zeddy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•