Hi there,

 

I have a bug with a timesheet calculation that determines daily hours worked. Hours less than 6 am and after 8 pm are excluded as they are treated as time in lieu.

 

For the following calculation the starting time where 5:30 am, the finishing time is 1:00 pm and the lunch break is .5 hours, the hours worked displays as 6 hours. It should be 6.5. The first 30 minutes (5:30 am to 6:00 am) are excluded as they are before 6 am which leaves a total of 7 hours minus .5 hours for lunch which is 6.5 hours. What have i done wrong? All replies gratefully received.

 

If(IsEmpty(StartTime24) = 0 and IsEmpty(EndTime24) = 0;

 

Round(

Hour( If(EndTime24 > Time ( 20 ; 0 ; 0 ); "8:00 PM"; EndTime24) ) - 

 

Hour( If(StartTime24  < Time ( 6 ; 0 ; 0 ); "6:00 AM"; StartTime24) ) 

 

+ (Minute( EndTime24)  - 

 

Minute(StartTime24) ) / 60 - 

 

Lunch Hours 24;

2)

+

If(

GetAsNumber(EndTime24) <= GetAsNumber(StartTime24);

24;

0)

; 0)

IMHO, your calculation is written in an unduly complex manner. The mistake you have made is at least partly due to this complexity. Your calculation of minutes takes into account the original StartTime24 - i.e. 5:30 am, instead of the corrected start time of 6:00. That's where the extra .5 hours is coming from.

 

This would have been avoided if you had written your formula in a more streamlined manner, say:

If ( not IsEmpty ( StartTime24 ) and not IsEmpty ( EndTime24 ) ;
Let ( [
start = Max ( StartTime24 ; Time ( 6 ; 0 ; 0 ) ) ;
end = Min ( EndTime24 ; Time ( 18 ; 0 ; 0 ) ) ;
h = ( end - start ) / 3600
] ;
Round ( h - Lunch Hours 24 ; 2 )
)
)

I am also puzzled by your last If(): it doesn't quite fit together with the "from 6 am to 8 pm" rule.

 

If(IsEmpty(StartTime24) = 0 and IsEmpty(EndTime24) = 0;

 

 

Hello BrainOnAStick (love the name by the way) :-)

 

Comment's suggestions (and calculation) are the ways to go  and I do not wish to distract from your current calculation rewrite but I wanted to explain this part for your future calculation considerations ...  IsEmpty ( StartTime24 )  = 0 

 

IsEmpty() is a test by itself so you are in essence asking "is the time empty" and then "does that result equal zero" actually reversing the logic.  There is a second issue as well but first the reversal:

 

A. If time is 3:14 ... is it empty?  No, so it fails the IsEmpty() test, producing 0 (false).  Now does that 0 equal 0?  Yes so this test passes.

B. If time is <empty> ... is it empty?  Yes, so it passes the IsEmpty() test, producing 1 (true).  Now does that 1 equal 0?  No, so this test fails.

 

So if you think you will get a TRUE if time is empty using  IsEmpty ( StartTime24 )  = 0  then that is ... ahm .. false.

 

But further, if your time field has only ZEROS then it will group with records with actual times.  So not only did the first reverse the logic I believe you were seeking but the second could potentially produce false positives depending upon whether the field was empty or had only zeros.  

 

C. If time is 0:00:00 ... is it empty?  No so it fails the IsEmpty() test, producing 0 (false).  Now does that 0 equal 0?  Yes so this test passes and ends up in group with A.

 

IsEmpty() isn't an absolutely safe test on number fields where a calculation or a person can have inserted zero(s).  IsEmpty() IS the test and it does not need an operator.  BTW, Comment tested the time fields by using a true Boolean (number) test instead of IsEmpty() which is completely safe with dates, times (if time is duration but not regular times because midnight is 0:00:00 ) and numbers.  If ANY number other than zero exists in start time AND if any number exists in End Time then it is true on both sides of the AND and thus true result.

 

And now back to your regularly scheduled programming ...  

 

EDITED:  Added reference to clarify Time fields in case someone does not read further.

Edited January 14, 201412 yr by LaRetta

 

Comment tested the time fields by using a true Boolean (number) test instead of IsEmpty() which is completely safe with dates, times and numbers.

 

Actually, Comment made a big mistake here which he's going to correct right away. You cannot use this test with Time, because an entry of midnight has a zero value and therefore will be considered to be the same as empty.

I'm afraid I don't understand your other point: the expression =

IsEmpty ( field ) = 0

though unnecessary convoluted, is logically equivalent to =

not IsEmpty ( field )

I see many people get twisted in its reversal logic when used with IsEmpty() thinking the = 0 is a confirmation of empty field because that is how it looks like it would evaluate.  I was (hopefully) showing how it reverses and how it is unnecessary and confusing. 

 

Midnight ... good catch.  So time as duration can be boolean-tested but not time as Time.  

Hey, the catch is yours. If you hadn't said "If ANY number other than zero exists in start time" I wouldn't have gone "Doh".

IMHO, your calculation is written in an unduly complex manner. The mistake you have made is at least partly due to this complexity. Your calculation of minutes takes into account the original StartTime24 - i.e. 5:30 am, instead of the corrected start time of 6:00. That's where the extra .5 hours is coming from.

 

This would have been avoided if you had written your formula in a more streamlined manner, say:

If ( not IsEmpty ( StartTime24 ) and not IsEmpty ( EndTime24 ) ;
Let ( [
start = Max ( StartTime24 ; Time ( 6 ; 0 ; 0 ) ) ;
end = Min ( EndTime24 ; Time ( 18 ; 0 ; 0 ) ) ;
h = ( end - start ) / 3600
] ;
Round ( h - Lunch Hours 24 ; 2 )
)
)

I am also puzzled by your last If(): it doesn't quite fit together with the "from 6 am to 8 pm" rule.

Firstly, thank you so much for this code. It has fixed the problem.

 

The code I was using was based on a very old Filemaker Starter Solution which is why it had the strange +24 hours at the end. 

 

Thanks again.

the strange +24 hours at the end.

 

Oh it's not strange at all; something like that is necessary when you have people working across midnight . Without it their calculated duration will come out negative. But it makes little sense to have it, when you're only counting hours between 6am and 8pm.