Date and Time Calc over multiple days

[orangej]

Hello,

 

Here is the deal as firefighters we work 24 hour shifts our CAD that is created in filemaker shows the shift either (RED,GOLD, BLACK) when a call (new record) is generated. The problem is for example today at 0700 is black shift until 0659 tomorrow at which point Red Shift starts and so on. I have a data table set to match dates with a lookup to enter the correct shift but between Midnight and 0659 it enters the wrong shift because of the date change. Is the any suggestions out there? Thanks in advance this is really causing tracking issues.

[Techphan]

What do you want to calculate? I would think hours (per shift) but have to ask.

 

Do you look at start and stop times like this?

 

Shift start: 1900 (7:00 pm)

Shift end: 0700 (next day 7:00 am)

Total hours: 12

 

One way is to calculate Start Time to midnight then midnight to End Time, then add the two time values together. The other is to use a Time-Date stamp for each and then to calculate by (TotalTime=EndDateTime - StartDateTime) which should give the answer in FM time, then convert to hours and minutes.

[orangej]

I'm sorry I didn't explain it all the way I think

 

I have a field called

 

Shift Day (lookup) from data table

that relates with the layout by date. date = date with the result of the shift day like before Red Gold or Black. Problem is it changes after midnight but I need to change after 659 am. With the result being the next shift

 

Example:

 

5-30-08 from 0700 to 0659 on 5-31-08 is Black Shift

from 5-31-08 0700 to 0659 on 6-1-08 is Red Shift and so on

 

problem is now like today at midnight when a new record is created it will perform the lookup as always and because it will be the 31st it will say Red Shift instead of black. It will be wrong on any record until 700 when red shift starts.

 

I don't know if that helps but thanks for any input you have.

[Techphan]

1) Can you post your application?

 

2) When is a new record generated? Is a new record created at various times throughout the day or just once at the beginning of the month for your schedule?

 

3) What fields are in your Lookup Table?