Overtime for Day Shift & Night Shift matching with Mon-Fri,Sat(half day) and Sun

[yen]

Day Shift:
Working Hour:
(Mon-Fri) 8.30am-5.30pm (8 hrs with 1 hr lunch break)
(Sat) 8.30am-12.30am

Overtime:
Rate x1.5 - (Mon-Fri) Before 8.30am & After 5.30pm
- (Sat) After 1.30pm (lunch break 12.30pm-1.30pm is not given as OT)

Rate x2.0 - SUNDAY, PUBLIC HOLIDAY



Night Shift:
Working Hour:
(Mon-Fri) 8.00pm-5.00am (8 hrs with 1 hr lunch break)
(Sat) 8.00pm-12.00am

Overtime:
Rate x1.5 - (Mon-Fri) Before 8.00pm & After 5.00am
- (Sat) After 12.00am
Rate x2.0 - SUNDAY, PUBLIC HOLIDAY





The calculation will be much more complicated for Night Shift Overtime.
For example:
sample.JPG
The OTx1.5 & OTx2.0 is the total hour works.

If the public holidays falls on Weekdays, the overtime calculation will be even more complicated:
sample.JPG

I have no idea how should I generate the formulas to calculate the overtime for this, anyone could help me?

[dogberry]

Hi

Can you post a sample worksheet so we can see the layout etc dont post confidentail information


To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.



Chris

[Pepe Le Mokko]

Nice pictures, but XL is about sheets, so please post sample sheet

[yen]

is this work?

[yen]

Anybody can help me?