Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

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

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > MIS/IT > Office Suites > Microsoft: Office Forum

MS excel dates to establish ages

thread68-1682767
Read More Threads Like This One
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 Dragon

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")x

Test 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 Dragon

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.

Cheers
Paul 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.
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Office

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close