Calculate school grade based on date of birth

**RegionV** · 10-12-2013, 12:16 PM

I am trying to fiqure out a formula to calculate a school grade based on the date of birth. I have a formula that will calcuate based on today's date but I cannot seem ti fiqure out how to change that to a fixed time frame such as 9/1/2013 - 6/30/14. Any help would be apreciated.

**shg** · 10-12-2013, 12:23 PM

What's the formula that you use successfully with today's date?

**RegionV** · 10-12-2013, 10:21 PM

I am attaching an excel spreadsheet with the formula's contained. I tried to change the date from todays date to a parameter of dates 9/1/13-6/30/14 and I keep getting an error. I am a novice at this.

**oeldere** · 10-13-2013, 03:44 AM

See the attached file.

Dutch =ALS(D5="";"";VERT.ZOEKEN(D5;Database!$A$3:$C$15;3;WAAR))

English=if(D5="","",VLOOKUP(D5,Database!$A$3:$C$15,3,TRUE))

I change the red collored text in your formula.

I put the formula in differant cells (the green ones) so you can see the differance.

Please reply.

**daddylonglegs** · 10-13-2013, 06:19 AM

Isn't the school grade based on age at a certain date? For example if you are aged 5 on 1st September then you are in Year 1 for the coming year

This formula will give you the grade based on that logic, without using a table

=TEXT(DATEDIF(C5,DATE(YEAR(B$1+122)-5,9,1),"y"),"""Year ""0;0;""Pre School""")

If you use that formula then grades will only alter when 1st September 2014 comes around. You may want those to alter earlier, e.g. up to 30th June you show the current/previous year grade, on or after 1st July you get the following year's grade. You can adjust the 122 figure to control that - so if you want 1st July as that cutoff change 122 to 184 (it's the number of days from the chosen date until the end of the year)

**RegionV** · 10-13-2013, 10:21 AM

Thank you this works for me. Much appreciated

**oeldere** · 10-13-2013, 10:39 AM

@RegionV

since you got 2 answers it is always good to add to whom you're replying.

**RegionV** · 10-13-2013, 11:00 AM

Oledere thank you it works.

**oeldere** · 10-13-2013, 11:05 AM

@RegionV

Did you also test the formula in #5 of daddylonglegs?

Calculate school grade based on date of birth

LinkBack

Thread Tools

Rate This Thread

Display

Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Re: Calculate school grade based on date of birth

Thread Information

Users Browsing this Thread

Similar Threads

Formula to Calculate Age based on date of birth

Calculate from Date of Birth

formula to calculate age using birth date and current date

[SOLVED] How can I calculate age using a birth date in Excel?

calculate the age of someone from their date of birth

Bookmarks

Bookmarks

Posting Permissions