Complex timesheet template!

Hi all!



I’ve got a complex billing template and would like automate the calculation of hours as much as possible, wondering if anyone can help take a look? J



The grey area is where should be calculated by formula and it will be calculated based on the hours spent on task A and B within a day.



Total regular hours is 8 hours for a day. After 8 hours, no matter what task I’m performing, all OT hours will fall to that particular task. For example, I worked on Task A from 9 am-12 pm, 1 pm-6 pm, and then had overtime work on Task B from 6-7pm, then my OT hour would fall to Task B (1 Hour), and if I worked on Task A again from 7-8pm, then there would be 1 hour OT for task A as well.



I have got the basic formula of calculating normal hour and OT hour per task, but I cannot figure out how to calculate the total hours based on the timeline of both tasks.



And for Saturday and Sunday, it is by default calculated as overtime hrs but sometimes it may be counted as regular hours if that day is an official working day. I’ve searched online and found out we can add a button (or any other better methods! J) to be clicked on to calculate base on different rate. Wondering if that’s possible to do this on the template (though it’s for the regular/OT hrs)?

Thank you!!