Home · Maps · About
Home > SubChat
 

[ Read Responses | Post a New Response | Return to the Index ]
[ First in Thread | Next in Thread ]

 

view flat

Re: Excel

Posted by Bill West on Sat Dec 9 02:26:16 2017, in response to Re: Excel, posted by BusRider on Thu Dec 7 22:49:30 2017.

Hi, Rider, here's a different approach. I've tried to solve split shifts and the 8 & 11 hours OT rules together. It gets long but the formulas are short and understandable. They could readily be merged into a single cell for each time but would be very hard to follow.

row........col E (align right)...................col F.....col G
4 arrival..............................................5:00
5 commence work................................5:10
6
7 start break......................................11:30 (if any)
8 end break.......................................14:10
9
10 finish day........................................20:05
11
12 total day..........................................15:05 =arrival to finish
13 break hours.......................................2:40 =break start to end
14 total work hours...............................12:25 =net of above
15 hours payable as ST under 8hr rule......8:00 =lesser of work hours and 8hrs
16
17 11 hrs on duty occurs at...................16:10 o'clock (or finish time if earlier)
18 total hours to that point....................11:10 =arrival to 11hr time
19 break hours to that point....................2:40 =(earlier of break start and 11hr time) to (earlier of break end and 11hr time)
20 work hours to that point....................8:30 =net of above
21 hours payable as ST under 11hr rule..8:30 =lesser of work hours to 11hr mark and 11hrs
22
23.....................................................8:00 =the ST hours worked under the more limiting rule
24 payroll ST =...................................8:00 =minimum day's pay
25 ....................................................4:25 =rest of the work hours
26 payroll OT =....................................4:30 =rounded up in 15 minute increments

The formulas in col F are: They are all custom formatted h:mm .
12 =F10-F4
13 =F8-F7
14 =F12-F13
15 =MIN(F14,"8:00")
16
17 =MIN(F5+"11:00",F10)
18 =F17-F4
19 =MIN(F8,F17)-MIN(F7,F17)
20 =F18-F19
21 =MIN(F20,"11:00")
22
23 =MIN(F21,F15)
24 =MAX(F23,"8:00")
25 =F14-F23
26 =CEILING(F25,"00:15")

This also brought out more questions about the contract terms:
1. Try a 6:55 break start, it makes the worked ST low. Would pay still be 8hrs ST then OT just as a premium?
2. In general, for 10hrs worked does the pay stub show 10hrs worked at ST rate and 2hrs of OT premium? Or does it show 8hrs ST and 2hrs OT rate?
3. For 6hrs worked, with half beyond the 11hr rule, does it show 3hrs ST and 3hrs OT? If so how is the 8hr minimum pay for the day shown? Is it 8hrs ST and 3hrs OT rate?
4. If 3hrs ST is indeed shown, would it be rounded up in 15min increments?
5. Is the 10 minutes at the start fixed or variable?

Bill

Responses

Post a New Response

Your Handle:

Your Password:

E-Mail Address:

Subject:

Message:



Before posting.. think twice!


[ Return to the Message Index ]