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 Expand viewCollapse view
`=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)