Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
From a list of dates of birth I need to show what school year they are in
today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
With a date of birth in A1, try this in B1:
=INT((TODAY()-A14+223+(MOD(YEAR(A14),4)=0))/365.25)-5 I've checked this for 1992 to 2003, and it seems to correctly distinguish different years at 1st September. Hope this helps. Pete On Jan 20, 3:03*pm, Bek wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Suppose you have DOB in A1
For current year, enter 8/31/2008 in B1 Enter this in C1 ==DATEDIF(A1,B1,"y") This will give you age in years as on 8/31/2008 Now you can check this in D1 like this =IF(C1=5,"KG",C1-4) assuming you do not have kids who are not in school otherwise =IF(C14,IF(C1=5,"KG",c1-4),"Too young to be in school) You can get a formula to give you 8/31/2008 if you do not want to enter a date once a year :-) "Bek" wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Sorry, I tested this on row 14 and forgot to change the references
when posting. It should read: =INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5 if you put it in B1. Hope this helps. Pete On Jan 20, 3:33*pm, Pete_UK wrote: With a date of birth in A1, try this in B1: =INT((TODAY()-A14+223+(MOD(YEAR(A14),4)=0))/365.25)-5 I've checked this for 1992 to 2003, and it seems to correctly distinguish different years at 1st September. Hope this helps. Pete On Jan 20, 3:03*pm, Bek wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated!- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
On Tue, 20 Jan 2009 07:03:06 -0800, Bek wrote:
From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! How old does someone have to be to enter school? What is the cutoff date? --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Ron Rosenfeld wrote: On Tue, 20 Jan 2009 07:03:06 -0800, Bek wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! How old does someone have to be to enter school? What is the cutoff date? --ron Not to mention the exceptional/special student not following that yearly schedule. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Ahh, well in the UK this rarely happens and for the group of people I am
analysing this shouldn't be an issue "Bob I" wrote: Ron Rosenfeld wrote: On Tue, 20 Jan 2009 07:03:06 -0800, Bek wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! How old does someone have to be to enter school? What is the cutoff date? --ron Not to mention the exceptional/special student not following that yearly schedule. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Hello
School years run from 1 - 14 with Reception and Nursery prior to that, however, the people I'm using will be from approx year 8 onwards. N - 4th birthday between 1st Sept and 31st Aug R - 5th birthday between 1st Sept and 31st Aug 1 - 6th birthday between 1st Sept and 31st Aug 2 - 7th birthday between 1st Sept and 31st Aug 3 - 8th birthday between 1st Sept and 31st Aug 4 - 9th birthday between 1st Sept and 31st Aug 5 - 10th birthday between 1st Sept and 31st Aug 6 - 11th birthday between 1st Sept and 31st Aug 7 - 12th birthday between 1st Sept and 31st Aug 8 - 13th birthday between 1st Sept and 31st Aug 9 - 14th birthday between 1st Sept and 31st Aug 10 - 15th birthday between 1st Sept and 31st Aug 11 - 16th birthday between 1st Sept and 31st Aug 12 - 17th birthday between 1st Sept and 31st Aug 13 - 18th birthday between 1st Sept and 31st Aug 14 - 19th birthday between 1st Sept and 31st Aug Year 14 is the last. cheers "Ron Rosenfeld" wrote: On Tue, 20 Jan 2009 07:03:06 -0800, Bek wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated! How old does someone have to be to enter school? What is the cutoff date? --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
no worries
it works perfectly for this year - does the 'today' bit ensure it will be correct next year? Also - what does the '223' bit mean?? Sorry to ask, I am self taught! "Pete_UK" wrote: Sorry, I tested this on row 14 and forgot to change the references when posting. It should read: =INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5 if you put it in B1. Hope this helps. Pete On Jan 20, 3:33 pm, Pete_UK wrote: With a date of birth in A1, try this in B1: =INT((TODAY()-A14+223+(MOD(YEAR(A14),4)=0))/365.25)-5 I've checked this for 1992 to 2003, and it seems to correctly distinguish different years at 1st September. Hope this helps. Pete On Jan 20, 3:03 pm, Bek wrote: From a list of dates of birth I need to show what school year they are in today, but have it so it is always correct in the future too. I can caculate their current school year using a vlookup table of dates of birth between Sept and August and the relevent school year, but come September 2009 this will be wrong. i.e. if born 30th January 1996 today you will be in school year 8. If I check the spreadsheet in January 2010 I need it to be telling me you are now in year 9. School year runs from 1st September to 31st August. Any help much appreciated!- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
On Wed, 21 Jan 2009 01:13:02 -0800, Bek wrote:
Hello School years run from 1 - 14 with Reception and Nursery prior to that, however, the people I'm using will be from approx year 8 onwards. N - 4th birthday between 1st Sept and 31st Aug R - 5th birthday between 1st Sept and 31st Aug 1 - 6th birthday between 1st Sept and 31st Aug 2 - 7th birthday between 1st Sept and 31st Aug 3 - 8th birthday between 1st Sept and 31st Aug 4 - 9th birthday between 1st Sept and 31st Aug 5 - 10th birthday between 1st Sept and 31st Aug 6 - 11th birthday between 1st Sept and 31st Aug 7 - 12th birthday between 1st Sept and 31st Aug 8 - 13th birthday between 1st Sept and 31st Aug 9 - 14th birthday between 1st Sept and 31st Aug 10 - 15th birthday between 1st Sept and 31st Aug 11 - 16th birthday between 1st Sept and 31st Aug 12 - 17th birthday between 1st Sept and 31st Aug 13 - 18th birthday between 1st Sept and 31st Aug 14 - 19th birthday between 1st Sept and 31st Aug Year 14 is the last. cheers Still not clear, but: Putting this together with your example of the child born 30 Jan 1996 being in the 8th year, I conclude that is the case since, by 9/1/2009, he will be 13 (he's only 12 now). That being the case, try this formula: =DATEDIF(A1,DATE(YEAR(A2)+(MONTH(A2)8),8,31),"y")-5 A1 contains the Date of Birth A2 contains the date against which you are testing (e.g. TODAY(), or some fixed date). Since you said the range of students would be in years 8-14, I did not bother to test for years outside of that range (e.g. for N, R, or too old) but that could be done with some nested IF statements. --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
On Tue, 20 Jan 2009 08:01:38 -0800 (PST), Pete_UK wrote:
Sorry, I tested this on row 14 and forgot to change the references when posting. It should read: =INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5 if you put it in B1. Hope this helps. Pete With your formula, someone born 1 Sep 93 would be in the 11th year; 2 Sep 93 would be in the tenth year. I think that is not correct based on the chart the OP provided with the cutoff date being 1 Sep. --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Yes, it was correct yesterday, but it has moved on by one day and will
continue to do so in its current form. I'll have another look at it tomorrow. Pete On Jan 22, 12:35*am, Ron Rosenfeld wrote: On Tue, 20 Jan 2009 08:01:38 -0800 (PST), Pete_UK wrote: Sorry, I tested this on row 14 and forgot to change the references when posting. It should read: =INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5 if you put it in B1. Hope this helps. Pete With your formula, someone born 1 Sep 93 would be in the 11th year; 2 Sep 93 would be in the tenth year. *I think that is not correct based on the chart the OP provided with the cutoff date being 1 Sep. --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Okay, with dates of birth starting in A1, put this in B1:
=DATEDIF(A1,DATE(YEAR(TODAY()),8,31),"y")-5 and copy down. Tested for various dates of birth spanning 1995 to 2003, and values of TODAY going out to 2013. Hope this helps. Pete On Jan 22, 2:08*am, Pete_UK wrote: Yes, it was correct yesterday, but it has moved on by one day and will continue to do so in its current form. I'll have another look at it tomorrow. Pete On Jan 22, 12:35*am, Ron Rosenfeld wrote: On Tue, 20 Jan 2009 08:01:38 -0800 (PST), Pete_UK wrote: Sorry, I tested this on row 14 and forgot to change the references when posting. It should read: =INT((TODAY()-A1+223+(MOD(YEAR(A1),4)=0))/365.25)-5 if you put it in B1. Hope this helps. Pete With your formula, someone born 1 Sep 93 would be in the 11th year; 2 Sep 93 would be in the tenth year. *I think that is not correct based on the chart the OP provided with the cutoff date being 1 Sep. --ron- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
On Thu, 22 Jan 2009 13:34:38 -0800 (PST), Pete_UK wrote:
Okay, with dates of birth starting in A1, put this in B1: =DATEDIF(A1,DATE(YEAR(TODAY()),8,31),"y")-5 and copy down. Tested for various dates of birth spanning 1995 to 2003, and values of TODAY going out to 2013. Hope this helps. Pete Not quite Pete. The problem with this approach is exemplified by the following, which includes some assumptions which might be US-centric. The school year likely begins 9/1 Given the OP's student DOB of 1/30/96, If Today = today (22 Jan 09) then he is in the 8th year. However, come 1 Sep 2009, he should be in his ninth year. Your formula will still return 8 up through 31 Dec 2009. I think my previously posted suggestion: =DATEDIF(A1,DATE(YEAR(A2)+(MONTH(A2)8),8,31),"y")-5 where A1: DOB A2: date to be tested, could be TODAY() will return the desired answer (if my various assumptions are correct). --ron |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate current school year from DOB ongoing?
Hi Ron,
yes, you're right - in my testing I was more concerned with ensuring the change of year group always happened on 1st September and didn't notice that the year groups hadn't changed with values of today beyond 1st Sept in any one year. I've tested your formula with my set-up and yours works. Drat !! <bg Pete On Jan 22, 10:11*pm, Ron Rosenfeld wrote: On Thu, 22 Jan 2009 13:34:38 -0800 (PST), Pete_UK wrote: Okay, with dates of birth starting in A1, put this in B1: =DATEDIF(A1,DATE(YEAR(TODAY()),8,31),"y")-5 and copy down. Tested for various dates of birth spanning 1995 to 2003, and values of TODAY going out to 2013. Hope this helps. Pete Not quite Pete. The problem with this approach is exemplified by the following, which includes some assumptions which might be US-centric. The school year likely begins 9/1 Given the OP's student DOB of 1/30/96, If Today = today (22 Jan 09) then he is in the 8th year. *However, come 1 Sep 2009, he should be in his ninth year. *Your formula will still return 8 up through 31 Dec 2009. I think my previously posted suggestion: =DATEDIF(A1,DATE(YEAR(A2)+(MONTH(A2)8),8,31),"y")-5 where A1: * * DOB A2: * * date to be tested, could be TODAY() will return the desired answer *(if my various assumptions are correct).. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Days Left in Current Year | Excel Worksheet Functions | |||
Formula to calculate the current year | Excel Discussion (Misc queries) | |||
calculate prior calendar year end from a moving current date | Excel Worksheet Functions | |||
School year repetitive listing | Excel Discussion (Misc queries) | |||
Formula for year in school? Freshman, Sophmore | Excel Worksheet Functions |