Time sheet calculations for Sunday work and public holidays.

Good day can someone please help me.

I have added the excel document I am working on, you will see on the first sheet it is my time sheet and on the second sheet it is my calendar and a list of the public holidays.

Okay what I need is the following: in the Colum that says “OT 2 (Sunday)” must display the hours only if that employee worked on that Sunday. The Colum that says “Hours” will determine the hours in the “OT 2 (Sunday)” Colum. Both of the Colum’s must show the same amount of hours worked for that particular day.

Secondly in the Colum that says “OT 3 (PPH)” must display the hours only if that employee worked on that particular PPH Day as on sheet 2. The amount of hours the employee will get in the “OT 3 (PPH)” Colum is a 0.3 of the amount of hours in the “Hours” Colum for that day on sheet 1.

Please don't change any of the other formulas I have in this document I need them all.

Hi try these

Create a Named Range for Your Public Holiday Dates

Call it "Hols"

Please Login or Register  to view this content.

CU13 copy Down

Please Login or Register  to view this content.

DD13 Copy Down

Please Login or Register  to view this content.

The Public Holiday Hours are only counted Monday to Friday

If you want it to include Saturday and Sunday change this part of the formula

from this <6
To this >0

thanks for the help it works perfectly.