date formula assistance req'd

  • Thread starter Thread starter Bri
  • Start date Start date
B

Bri

I'm hoping to get help with the following worksheet formula. Given a
student's Date of Birth in, say A2, I need the formula in B2 to do the
following:

If today's date is July 1 or later, the formula needs to give the student's
age in years on Dec 31 of the current year, but if today's date is earlier
than July 1, the formula needs to give the students age in years on Dec 31
of the previous year.

eg Today is May 5, so ...
a birthdate of Mar 15, 1998 gives an age of 7 (her age as of Dec 31,
2005) and
a birthdate of Nov 10, 1998 gives an age of 7 (her age as of Dec 31,
2005)

but on Sep 1,
a birthdate of Mar 15, 1998 gives an age of 8 (her age as of Dec 31,
2006) and
a birthdate of Nov 10, 1998 gives an age of 8 (her age as of Dec 31, 2006).

Thanks, Bri
 
Hi!

Try this:

=DATEDIF(A2,IF(AND(MONTH(TODAY())>=7,DAY(TODAY())>=1),DATE(YEAR(TODAY()),12,31),DATE(YEAR(TODAY())-1,12,31)),"y")

Biff
 
After seeing Peo's formula I realize that it doesn't matter what the day is.
So this will do:

=DATEDIF(A2,IF(MONTH(TODAY())>=7,DATE(YEAR(TODAY()),12,31),DATE(YEAR(TODAY())-1,12,31)),"y")

Biff
 
This could be further refined to:

=DATEDIF(A2,DATE(YEAR(TODAY())-(MONTH(TODAY())<7),12,31),"y")

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate End Date Using Start Date and No. of Days excluding specific days. 0
Calulating Age Formula 3
Test if a date falls within a given range of dates in Excel? 6
subtracting dates 1
Conditional Accounts receivable Aging Analysis 0
Date Formulas 2
If statements?? 2
date functions 4

Back
Top