date formula assistance req'd

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

"Bri" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>

One way

Assume the DOB is in A1

=IF(A1="","",DATEDIF(A1,DATE(YEAR(TODAY())+LOOKUP(MONTH(TODAY()),{0;7},{-1;0}),12,31),"y"))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Bri" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "Bri" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>
>

thanks to both
Bri

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:%[email protected]...
> One way
>
> Assume the DOB is in A1
>
> =IF(A1="","",DATEDIF(A1,DATE(YEAR(TODAY())+LOOKUP(MONTH(TODAY()),{0;7},{-1;0}),12,31),"y"))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Bri" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>
>

This could be further refined to:

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

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>> "Bri" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>>
>>>
>>>
>>
>>
>
>