Lunch out - Clock in + Clock Out - Lunch in
Will compute the total elapsed time in seconds.
( Lunch out - Clock in + Clock Out - Lunch in ) / 3600
will convert seconds into hours. (Instead of dividing by 3600, you can also choose Time as the result type and format the field to display HH::MM::SS)
Use TimeStamp fields instead of time fields if there is any chance that this interval might cross the midnight hour.
PhilThank you for the quick response. I am still getting nonsensical output from these equations, and I'm not sure why. To save time I have some screen shots to show you what I see.There is a screen shot of the formula, as entered, and then a series of shots as I push the various time buttons.
Calculation result should be number, but that's not the cause of the incorrect result.
Are the four data field of type time or are they text?
When I type in these 4 time values, I get a result of .0061111111111111
Since I get the same result you do when I just enter IN and Go to Lunch figures, (.0033333333333333) I'd look at the data types for the other two fields here and also make sure that the fields placed on the layout are the ones used in the calculation.
Ok, figured a couple of things out.
The field does not update until it, (or something else is) selected. In other words when I press the Clock Out button, and it populates in the value, it does not update the Total Hours value until you click on anything else. This effect was causing each of the calculations in the screen shots, to be behind one calculation set.
I am sure I can resolve this with a script on the buttons, and possibly programing in the calculations at that level, so that when you clock out for lunch it updates correctly to show total hours worked prior to lunch, and then when you clock out at the end of the day it updates to include all hours worked.
All of the crazy data on the in between was the times getting divided by 3600, after being subtracted from 0, basically allowing every other calculation to make no sense. If I do decide to leave it as a calculation embedded in the Total Hours field then I can use an IF statement to make sure it does not do this.
All of the fields in the calculations were on the layout, however they where not all set to time, I changed them all to time.
Is there a simple way to force the field to update after each field input occurs.
Thanks for your help. Sometimes it's hard to see the forest for the trees.
It updates for me each time I exit a field by tabbing into the next field. It stays up to date, but treats empty fields as zero so I get negative values whenever a person clocks in and hasn't yet clocked back out...
You could modify this calculation to be:
( If ( Lunch out ; Lunch out - Clock in ) + If ( Clock Out ; Clock Out - Lunch in ) ) / 3600
Then you won't get negative values.
And you may want to consider using two records with an in and out date stamp field in place of one record with these four fields. That allows more easily for situations where a person works for part of a day and then leaves instead of taking a lunch break. A Summary field can total up the hours for any given day in order to get a daily total.
Using date stamps in place of time insures correct results even when some one works past midnight.