Works 3.0: Weekly Time Sheet Template Incorrect If Shift Spans 2 Days (128776)

SYMPTOMS

When you use a Weekly Time Sheet template in Works, the time sheet doesn't correctly calculate hours worked if the shift begins on one day, and ends on another. For example, a 10 P.M. to 7 A.M. shift will not calculate correctly.

CAUSE

The current formula, entered in cells C13:G13, C16:G16, C19:G19, C22:G22, and C25:G25, is

   =HOUR(Time Out-Time In)+MINUTE(Time Out-Time In)/60

where Time In refers to the cell containing the value for Time In, and Time Out refers to the cell containing the value for Time Out.

This formula cannot correctly calculate hours in shifts that span 2 days. It returns a value for Hours Worked much larger than the actual value.

RESOLUTION

The following formula checks if the value of Time Out is greater than the value of Time In, then calculates the total hours differently if the shift includes two days.

   =IF(Time In,IF(Time Out>Time In,(Time Out-Time In)*24,(1-Time In+Time
   Out)*24),0)

For example, in cell C13 you would enter the following formula:

   =IF(C11,IF(C12>C11,(C12-C11)*24,(1-C11+C12)*24),0)

MORE INFORMATION

The formula listed above checks if the Time Out value is greater than the Time In value. If it is, Time In is subtracted from Time Out, with the resulting value multiplied by 24 (because there are 24 hours in a day).

If Time In is greater than Time Out, Time In is subtracted from one (this compensates for the fact that we have one extra day involved). Then, Time Out is added to this total, and multiplied by 24.

The additional If statement is necessary in case no value is entered for Time In.

NOTE: By default, rows 13, 16, 19, 22, and 25 in the Weekly Time Sheet template are hidden and protected. To view and change the formulas in these rows, unhide the rows and turn off the Protect Data option.

To Unhide the Rows

From the Edit menu, choose Select All.
From the Format menu, choose Row Height.
Select the Best Fit check box and choose OK.

To Turn Off Protect Data

From the Format menu, choose Protection.
Select the Protect Data check box to clear it and choose OK.