Using date rages in calculation fields

[brainonastick]

Hi there,

I am trying to build in a series of 10 text message fields that tell a user if a course date falls within one of the 4 school holiday periods of a year. There are 10 course dates in a repeating field. The school holiday fields are global date fields. The calculation formula for message 1 is as follows:

Case((DayOfYear ( All_Course_Dates[1] ) ? DayOfYear ( Easter_Break_Ends ) and DayOfYear ( All_Course_Dates[1] ) ? DayOfYear ( Easter_Break_Starts )); "Easter Break";

(DayOfYear ( All_Course_Dates[1]) ? DayOfYear ( Winter_Break_Starts) and DayOfYear ( All_Course_Dates[1] ) ? DayOfYear ( Winter_Break_Ends )); "Winter Break";

(DayOfYear ( All_Course_Dates[1] ) ? DayOfYear ( Spring_Break_Starts ) and ( All_Course_Dates[1] ) ? DayOfYear ( Spring_Break_Ends )); "Spring Break" ; DayOfYear ( All_Course_Dates[1] ) ? DayOfYear ( Summer_Break_Starts ); "Summer Break"; "")

Im having no luck and all I get is the "Summer Break" result in some cases. Is there a better way of doing this? I cant find any "Date Range"formula steps which would be very handy - that is a formula that gives a true/false result when a date is compared to a start period and end period date.

Cheers,

brainonastick

[brainonastick]

The above pasted text has replaced the 'greater than or equal to' and 'less than or equal to' signs with 'question marks' for some reason. heres the formula again with the <= and >= reentered.

Case((DayOfYear ( All_Course_Dates[2] ) <= DayOfYear ( Easter_Break_Ends ) and DayOfYear ( All_Course_Dates[2] ) >= DayOfYear ( Easter_Break_Starts )); "Easter Break";

(DayOfYear ( All_Course_Dates[2]) >= DayOfYear ( Winter_Break_Starts) and DayOfYear ( All_Course_Dates[2] ) <= DayOfYear ( Winter_Break_Ends )); "Winter Break";

(DayOfYear ( All_Course_Dates[2] ) >= DayOfYear ( Spring_Break_Starts ) and ( All_Course_Dates[2] ) <= DayOfYear ( Spring_Break_Ends )); "Spring Break" ; DayOfYear ( All_Course_Dates[2] ) >= DayOfYear ( Summer_Break_Starts ); "Summer Break"; "")

Cheers

brainonastick

[Ender]

I would approach this a little differently. Use a second table to hold the Holiday ranges, and relate to that range with a multi-criteria relationship:

Main::All_Course_Dates >= Holiday::Holiday_Start_Date

AND Main::All_Course_Dates <= Holiday::Holiday_End_Date

If there is a match for this relationship, then the All_Course_Date is in the range of a holiday. You can test this a calculation:

Holiday? (calculation, number result) = not isempty(Holiday::RecordID)

I don't know how this would behave with your repeating field. I would get rid of the repeating field and use another table for the Course_Dates.

[transpower]

You need to use the CalculationRepetitionNumber. Something like:

Let ( [ R = Get ( CalculationRepetitionNumber ) ] ; Case ( R = 1 ; Case ....

etc.