Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

#### Feedback

"...I've learned more from your forums in 3 days than 3 months at school and on the job combined..."

#### Geography

Where in the world do Tek-Tips members come from?

# MS excel dates to establish ages

 Forum Search FAQs Links Jobs Whitepapers MVPs
 Allilue (TechnicalUser) 10 May 12 4:03
 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 (IS/IT--Management) 10 May 12 12:15
 =IF(TODAY()-A2<2,"Less then 2","More then 2")A2 is the location of the birth date. Blue If I wasn't Blue, I would just be a Dragon...
 Gruuuu (Programmer) 10 May 12 12:16
 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=IF(YEARFRAC(TODAY(),A2)<2,"IS UNDER TWO","AIN'T UNDER TWO")xTest it by putting 5/10/2010 and 5/11/2010 in your dates.
 bluedragon2 (IS/IT--Management) 10 May 12 12:21
 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 If I wasn't Blue, I would just be a Dragon...
 lionelhill (TechnicalUser) 11 May 12 5:39
 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 (Programmer) 11 May 12 9:34
 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 (TechnicalUser) 12 May 12 16:29
 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 (TechnicalUser) 13 May 12 0:53
 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. CheersPaul Edstein[MS MVP - Word]
 lionelhill (TechnicalUser) 14 May 12 6:07
 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.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!