I am writing a database using FMPro 5 for a school tracking project. I need to convert the actual date into a year range related to the current school year. For example,

Jan 22, 1999 becomes 1998-1999

March 1, 2000 becomes 1999-2000

Since the school year runs July 1 to June 30th. I've been trying to do it using a series of IF/THENs in a script after breaking apart the date into month and year. Is there an easier way?

Try using the following calculation for the School Year field (Result should be text)

Case(vDate > 731031, "2002/03", vDate > 730666, "2001/02" , vDate > 730301, "2000/01", vDate > 729936, "1999/00", "1998/99" )

vDate is the your date field.

You may add as many years as you like ot this calculation.

The large numbers are the day numbers that Filemaker uses for calculations. Entering dates as 30/6/2000 will not work. 30/6/2001 has a day number of 730666. You can get othe years by addingor subtracting 365 (Except of course for leap years!)

Hope this helps.

I would suggest creating a calculation field rather than a script to do the job in question. The calculation could look something like the following:

Case(Month(Status(CurrentDate))<7, Year(Status(CurrentDate))-1 & " - " & Year(Status(CurrentDate)),

Year(Status(CurrentDate)) & " - " & Year(Status(CurrentDate))+1)

The CASE function, similar to the IF function evaluates the month of the current date. It then generates the text result to show the fiscal year you are in.

Hope this helps.

Sorry if my post wasn't clear. I was indeed suggesting using a calculation field using the calculation I gave. Birdman's calculation is probably neater.