13 Replies Latest reply on Feb 10, 2017 6:14 AM by beverly

    Calculating school year based on date of birth

    james239

      Title

      Calculating school year based on date of birth

      Post

      I am trying to create a calculated field for a person's school year (UK) based on their date of birth falling between two dates of birth, ie. 1st September 2003 and 31st August 2004

       

      I have tried the following:

       

      If ( dob > 1/9/2003 and dob < 31/8/2004 ; 1 ; Other )

       

      Hoping that 4/2/2004 would return a 1, but it returns Other

       

      Any help please......

        • 2. Re: Calculating school year based on date of birth
          james239
            

          Thank you!

           

          Here is the working calculation based on your helpful link.

           

          If ( date_of_birth > Date ( 1 ; 9 ; 2003 ) and date_of_birth < Date ( 31 ; 8 ; 2004 ) ; "1" ; If ( date_of_birth > Date ( 1 ; 9 ; 2002 ) and date_of_birth < Date ( 31 ; 8 ; 2003 ) ; "2" ; "Other" )) 

           

          I will now just add the If's to get all the school year groups - unless there is an easier way? 

          • 3. Re: Calculating school year based on date of birth
            comment_1
              

            It would be better to use the Case() function instead of nested If's.

             

             

            More importantly, your calculation is hard-coded to a particular year. I am not sure what a "school year" is, but I would look for a way to calculate this using only the DOB. For example:

            Year ( DOB ) + 6 + ( Month ( DOB ) ≥ 9 )

            will return "2009" for people born between Sep 1, 2002 and Aug 31, 2003, inclusively.

            • 4. Re: Calculating school year based on date of birth
              BobSmith

                   I know this is an old thread but I have a similar problem.  Pupils attending school in the UK are placed into Year Groups depending on their date of birth. ie:

                   DoB 1 September 2000 - 31 August 2001 11/12  will be Year 7

                   DoB 1 September 1999 - 31 August 2000 12/13  will be Year 8

                   DoB 1 September 1998 - 31 August 1999 13/14  will be Year 9

                   DoB 1 September 1997 - 31 August 1998 14/15  will be Year 10

                   DoB 1 September 1996 - 31 August 1997 15/16  will be Year 11

                   I have a calculation: "Year " + ( Year(Get ( CurrentDate ) ) - Year( Pupil Date of Birth ) - 5 - ( Month( Pupil Date of Birth ) >= 9 ) )

                   but when the year changes the Year group is incorrect.

                    

                   Any help would be greatly appreciated.

                    

              • 5. Re: Calculating school year based on date of birth
                dansparks

                I am also struggling with creating a formula that works out school year based on date of birth.

                I have tried to build it using If functions - but I don't know if I'm using it right.

                 

                Is it IF(test case;result if correct;result if incorrect)?

                I currently have:

                Year ( Get(CurrentDate) ) - If (Birth Month≥9;Year(DOB)+5;Year(DOB)+4)

                 

                I have a field called Birth Month that gives a number for their month, so September is 9, and a dd/mm/yyyy field called DOB.

                 

                Can anyone tell me what is wrong with the above? It isn't working, and I'm presuming I'm using the If statement incorrectly.

                 

                Thanks

                • 6. Re: Calculating school year based on date of birth
                  fmpdude

                  OK, so I'm assuming that as each new year comes along, the years in your date list would also increment.

                   

                  2016:

                        DoB 1 September 2000 - 31 August 2001 11/12  will be Year 7

                        DoB 1 September 1999 - 31 August 2000 12/13  will be Year 8

                        DoB 1 September 1998 - 31 August 1999 13/14  will be Year 9

                        DoB 1 September 1997 - 31 August 1998 14/15  will be Year 10

                        DoB 1 September 1996 - 31 August 1997 15/16  will be Year 11

                   

                  So, for 2017:

                        DoB 1 September 2001 - 31 August 2002 11/12  will be Year 7

                        DoB 1 September 2000 - 31 August 2001 12/13  will be Year 8

                        DoB 1 September 1999 - 31 August 2000 13/14  will be Year 9

                        DoB 1 September 1998 - 31 August 1999 14/15  will be Year 10

                        DoB 1 September 1997 - 31 August 1998 15/16  will be Year 11

                   

                  Is that correct?

                   

                  If so, I would use a CASE statement using the number of years as an offset.

                   

                  The offset is always the same for Year 7 as the first example, and generalizes for the other DoB ranges. So, for the Year 7, it's just (the months would be hard-coded so just worry about the years):

                   

                  (the current year - 16) going through (the current year - 15).

                   

                  So for 2016 that calculation means 2000 - 2001

                  For 2017 that calculation means 2001 - 2002.

                   

                  ---

                   

                  The rest is just FMP syntax, which you can work out in the data viewer, right?

                   

                  I know FileMaker doesn't have a convenient "Between" function, but this is easy enough using a CASE statement and regular date math.

                   

                  Make sense?

                   

                  HOPE THIS HELPS.

                  • 7. Re: Calculating school year based on date of birth
                    fmpdude

                    Focus on the date offsets and making sure the numbers work by hand before jumping in and struggling with FMP syntax.

                     

                    A famous programmer once said: "If you can't figure it out on the back of an envelope, don't expect the computer to do it for you...".

                     

                    HOPE THIS HELPS.

                    • 8. Re: Calculating school year based on date of birth
                      philmodjunk

                      How isn't it working? That can be useful--to compare the result you get to the result you expect, but we don't know what result you expect.

                       

                      If this were executed today and birth month is 9 with Year ( DOB ) producing 2010, it will return a value of:

                      2016 - ( 2010 + 5 ) = 1

                       

                      But I don't know what value you should be getting or if you are getting a completely different result such as a ? result.

                       

                      Make sure that Birth Month is a field of type number and that DOB is a field of type date or you will get calculation errors.

                      • 9. Re: Calculating school year based on date of birth
                        beverly

                        LOL! so true. too many errors are carbon-based.

                        beverly

                        • 10. Re: Calculating school year based on date of birth
                          MartinBridges

                          Here's my solution for UK schools ie any school with a year intake 1st Sept to 31 Aug .

                          Took me the best part of an afternoon to get my little brain around this...

                           

                          Year ( Get(Current Date) ) - Year ( DOB ) - 5

                          + (( Month(DOB) < 9) * ( Month(Get(CurrentDate)) >= 9))             //  add a year if DOB < 9 and Current Date >= 9

                          - (( Month(DOB) >= 9) * ( Month(Get(CurrentDate)) < 9))              //  subtract a year if DOB >= 9 and Current Date < 9

                           

                          Note: The functions ( Month(DOB) < 9)  and  ( Month(Get(CurrentDate)) >= 9) etc are either true or false so = 1 or 0

                           

                          Martin

                          1 of 1 people found this helpful
                          • 11. Re: Calculating school year based on date of birth
                            fmpdude

                            Or, put another way, "between the keyboard and the chair".

                            • 12. Re: Calculating school year based on date of birth
                              IT_User

                              On a possible unrelated note, but after reading this question: unknown

                              Dealing with leap years in accounts

                               

                              There is the UK format of DD/MM/YYYY and FileMaker's format of MM/DD/YYYY

                               

                              I see that MartinBridges addresses this.

                              • 13. Re: Calculating school year based on date of birth
                                beverly

                                regardless, the Date() function, if used, takes the parameters as:

                                month ; day ; year

                                The format of the result depends on the date format that was in use when the database file was created. In the United States, dates are generally in the format MM/DD/YYYY. You can change the date format in your operating system.

                                You can change how the date is displayed by assigning a different date format to the field in Layout mode. Changing the formatting in this way only affects the way the data is displayed, not how it is stored.

                                beverly