Timesheet calculations

[brainonastick]

I am building a timesheet database and have hit a glitch in the calculation fields. 

 

We have 2 types of hours - flexitime and time in lieu which is time worked before 6 am and after 8pm.

 

All time between 6 am and 8pm is called flexitime.

 

I have adapted the timesheet calculations from the Time Cards DB in FM Starter Solutions but have added some code so that any start time < 6:00 AM or > 8:00 PM is excluded from the daily flexitime.

 

Here's one of the calculations for Hours That Day:

 

 

If(IsEmpty(StartTime4) = 0 and IsEmpty(EndTime4) = 0;

 

Round(

Hour( If(EndTime4 > "8:00 PM"; "8:00 PM"; EndTime4) ) - 

 

Hour( If(StartTime4  < "6:00 AM"; "6:00 AM"; StartTime4) ) 

 

+ (Minute( EndTime4)  - 

 

Minute(StartTime4) ) / 60 - 

 

Lunch Hours 4;

2)

+

If(

GetAsNumber(EndTime4) <= GetAsNumber(StartTime4);

24;

0)

; 0)

 

Any start time less than 9:59 AM works fine.

 

However if I enter a start time of 10:00 AM or more to whole thing goes haywire for some reason. 

 

Screenshot 1 attached shows the calculation working for a start time of 9 am (look at Friday 6/12/13 to see this). The Hours That Day are 6.5 which is correct.

 

Screenshot 2 shows what happens if the start time is set to 10 am. The Hours That Day change to 9.5 when they should be 5.5.

 

What am I doing wrong?

[comment]

I haven't really delved into your formula, but one thing stands out: "8:00 PM" is text, not time. Use the expression =

Time ( 20 ; 0 ; 0 ) 

instead.

 

 

Unrelated to your question, but you should use a separate related record for each entry, instead of numbered fields. That means one calculation instead of six or seven and - more importantly - the ability to summarize the entries any way you like.