Need calc for determining "Class of..." year, please

[Rich S]

Howdy, all:

This is a little tricky: I need a calc that will determine the year a student will graduate from high school...which I thought was simple enough by using a string of multiple Case statements, e.g.:

Case (

GradeLevel__lxn = "5"; Year(Get(CurrentDate)) + 7 or

GradeLevel__lxn = "6"; Year(Get(CurrentDate)) + 6 or

GradeLevel__lxn = "7"; Year(Get(CurrentDate)) + 5 or

...

)

...but the problem is the result will differ by a year if the calc is made before January 1 vs. on or after January 1. For example, if a student is in 6th grade in the current school year 2012-2013, he will be in the class of 2019 (2013 + 6); if I were to run the calc now it would output the year 2018. So, how to I phrase the calc to take into account the division of years?

TIA for your help!

[comment]

When do you change the grade levels?

[doughemi]

Try

Let(

EOSY= If(Month(Get(CurrentDate))>6; Year(Get(CurrentDate)) + 1; Year(Get(CurrentDate)) ) ;

EOSY +12 - GradeLevel_lxn)

EOSY stands for End Of School Year. This assumes that the grade level will be increased after end of school in June.

(or not. The grade level field will have to account for the student being held back after flunking his/her current grade.)

[Rich S]

Brilliant! Thanks, Doug.

Comment: Grade levels change annually in July or August when we run the End of Year function in our SIS (Student Information System) software; it automatically promotes students, then. (It's a product called PowerSchool, but we do all the data work in FileMaker.)

[doughemi]

Grade levels change annually in July or August when we run the End of Year function in our SIS (Student Information System) software; it automatically promotes students, then.

If you are going to use this field in the period between July 1 and when you run the EOY function, you should replace the 6 in my calc to month(EOYRunDate).

[comment]

Grade levels change annually in July or August

Well, then you'll probably want the year to "jump" on September 1? Say =

Let (

today = Get ( CurrentDate ) 

;

Year ( today ) + 12 - GradeLevel + ( Month ( today ) > 8 )

)