a calculation field for a range of times

[vyoung]

I have a billing database. I want a calculation field that tells me if the case that was done was a ON CALL case. All day Saturday and Sunday are ON CALL times. Any case during the week that is between 1800 and 0559 is also ON CALL. I can get a ON CALL field to post a Y for yes when it is saturday or sunday (I have a DateToDay field), but I can't figure out how to put in the range of times for the other days. I am driving at some command like if(StartTime is between 1800-0600, "Y", "N") When I try something like this they are all marked Y because I am guessing that it does not figure out the range correctly by the way I entered it. Here is what I have so far.

If( DayOfWeek = "Saturday", "Y", If( DayOfWeek = "Sunday" , "Y" , If(Hour(Start Time )<> 6-18,"Y","N" )))

If I take off the last if then statement, and put an "N", it will correctly mark all Saturday and Sundays as ON CALL. When I add the last if then it marks them all Y. Any ideas?

Thanks

[SteveB]

If( DayOfWeek = "Saturday", "Y", If( DayOfWeek = "Sunday" , "Y" , If(Hour(Start Time )<> 6-18,"Y","N" )))

Try this:

Case(DayOf Week= "Saturday or DayofWeek= "Sunday" or Hour >= 18 and Hour <= 6,"Y","N")

[vyoung]

Case(DayOf Week= "Saturday or DayofWeek= "Sunday" or Hour >= 18 and Hour <= 6,"Y","N")

This did not work either. It picked all the Sat and Sun, but the rest were marked N. There must be a problem with the 24 hour time convention. After fooling around numerous times and changing things I came up with the following calculation which appears to work so far.

Case(DayOfWeek= "Saturday" or DayOfWeek= "Sunday" or 24- Hour(Start Time) > 19 or Hour(Start Time) >= 18,"Y","N")

[vyoung]

Thanks for heading me in the right direction.

[LiveOak]

Try:

Case(DayOfWeek= "Saturday" or DayOfWeek= "Sunday" or Time < Time(6,0,0) or Time > Time(18,0,0),"Y","N")

-bd

[vyoung]

That worked. Can you give a brief explanation of why that worked and the other ways did not? Thanks.

[LiveOak]

While I have no exact knowledge of the internal working of FM, different field types have different storage formats internal to FM. When you enter "Time > 16", FM doesn't do a type conversion from the text "16" to the internal format used for 4 PM. What does "16" mean as a time, anyway? 16 hours?, 16 minutes?, 16 seconds? What if we entered 34 as a time? or 34.46? To skip this whole issue, times are entered explicitly using the Time(x,x,x) function. This made checking for validity is much easier for the designers of FM.

-bd