MS excel dates to establish ages

[Allilue]

Hi again,
hoping i can get some help here...

if I have a list of names and birthdays to go along, and i want to use a formula that lets me know if they are "under 2 years", how can i do this? i think i'm getting confused when it comes to leap years etc but is this even relevant?

thanks in advance

 

[bluedragon2]

=IF(TODAY()-A2<2,"Less then 2","More then 2")

A2 is the location of the birth date.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Gruuuu]

Nah that doesn't matter unless you're counting number of days. If you're just going by birthday, it's easy.

Assuming your data starts in A2, and assuming you don't care about the birth time of day
[tt]
=IF(YEARFRAC(TODAY(),A2)<2,"IS UNDER TWO","AIN'T UNDER TWO")x
[/tt]

Test it by putting 5/10/2010 and 5/11/2010 in your dates.

 

[bluedragon2]

Never mind that last post, you can change the 2 to 730, but as you said, it will not take account leap years. Let me look at the logic.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[lionelhill]

Since you want to check whether the calendar date 2 years after birth has been reached, and the actual distance in time from birth to the current calendar date is unfortunately variable because of whether it includes a leap-year or not, it might be necessary to check the actual calendar situation:

=IF((DAY(TODAY())+MONTH(TODAY())*50+YEAR(TODAY())*1000-(DAY(A1)+MONTH(A1)*50+YEAR(A1)*1000))>1999, "yes", "no")

This reconstructs a new date-signature value that is no longer directly related to time, but allows a comparison that ranks years as more important than months, which are more important than days. It then looks to see if the date signature has reached 2 years' worth.

There are probably tidier solutions, and of course you could make a user-defined function for the date signature conversion, which is repeated twice in the formula above.

 

[Gruuuu]

Leap years aren't relevant. Go by birthdays. Try telling a mother that their child isn't 2 yet "because of the Leap Year, you see..."

Even if the child is born on a leap day, the formula I posted will work for them.

 

[lionelhill]

Surely leap years do matter.
If your child is born on 2nd February and you look at how old they are on the following 1st of February it should be 364 days (i.e. they have not yet celebrated their 1st birthday. If, however, the year contained a leap day, then the answer will be 365 days, suggesting that today is their birthday and they are now 1. This means that merely subtracting birthdate as an Excel date from today as an excel date doesn't actually reveal whether today is my 1st birthday, or tomorrow is... The same applies to all subsequent years. I think?

 

[macropod]

lionel,

The whole point of Gruuuu's use of YEARFRAC is that it takes account of leap years. In that context, therefore, they are of no consequence and your comment "This means that merely subtracting birthdate as an Excel date from today as an excel date doesn't actually reveal whether today is my 1st birthday, or tomorrow is..." true as it may be, doesn't apply to Gruuuu's solution.

Cheers
Paul Edstein
[MS MVP - Word]

 

[lionelhill]

Oh, sorry, didn't really read properly. What a useful function. I'm afraid I still spend most of my life in Excel2003. Sadly 90% of the people I work with still open their workbooks routinely in Excel2003, and blithely assume the compatibility warning doesn't mean anything, so I can't use 2007 or 2010. Wish I could - there are so many useful features.