[SOLVED] Timesheet that Calculates overtime after 05:30 pm

[charmar]

I have created a time sheet but I need a formula for the following scenario: Any hours over 40 hours oer week is overtime AND any hours past 5:30 pm are overtime. I can easily do the formula for the over 40 hours but not for the past 5:30 pm. Can anyone help me?

Sorry everyone. I have attached the timesheet. I don't have to calculate pay, just hours worked.

[RusselB]

Presuming the following: Start Time is in column A, End Time is in column B, End Time will be before midnight of the same day, over-time is calculated at time and a half.

Code: Select all

=B2-A2+IF(B2>TIME(17;30;0);B2-TIME(17;30;0))*1.5

will give you the correct number of hours, regular and over-time (if applicable) for that shift.

If you want/need help with incorporating this with your current spreadsheet, as your formula for calculating over-time after 40 hours will need to be changed, the attach a sample of your spreadsheet.

[Villeroy]

A database would be the only adequate tool for this.
The attached spreadsheet is not easy to handle.

[Villeroy]

You may have noticed 2 issues with my spreadsheet draft:
1. Some overtime hours are calculated twice as late hours after 17:30 and weekly hours >40 as well
2. Using the weeknum function to identify the weeks should not be done like that. Notice that the hours of 2018-12-31 belong to the first week of 2019 but they are aggregated with the hours of the first week in January. The weeknum function follows the ISO standard which counts the week with more than 3 days as first week of next year or last week of the old year.
Instead of =WEEKNUM($B2) in column G you can insert this ...

Code: Select all

=YEAR($B2+IF(AND(WEEKNUM($B2;1)=1;MONTH($B$2)=12);1;IF(AND(WEEKNUM($B2;1)>51;MONTH($B$2)=12);-1;0)))&"-"&TEXT(WEEKNUM($B2;1);"00")

... and then refresh the pivot on Sheet2. The pivot's year column is obsolete then. The weeks are still identified by ISO standard with Monday being the first day of week.

Problem 1. is not that easy to handle. May be you want it like that. May be you want to not count late hours summed up with the week hours.
Problem 1. can be handled by subtracting the late hours on Sheet2: Sheet2.F5 =MAX(0;$D5-40/24-$E5)

[charmar]

I am so sorry for taking nearly a year to reply.I was off of this project for a long time. Thanks so much Villeroy! I was able to use the information you provided as a guideline and with some tweaks I was able to make it work.