| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date formula assistance req'd
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date formula assistance req'd
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date formula assistance req'd
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" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date formula assistance req'd
thanks to both
Bri "Peo Sjoblom" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date formula assistance req'd
This could be further refined to:
=DATEDIF(A2,DATE(YEAR(TODAY())-(MONTH(TODAY())<7),12,31),"y") Biff "Biff" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Trending Formula Results by Date | Excel Worksheet Functions | |||
| Year-to-date year to date formula | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Linear Mode
