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!!

Sorry for the inconvenience brought! Will take note of this, thank you!

Thank you so much for the advice!!! Never thought of creating helper columns to calculate it >< Here are my questions regarding the formula:

1. In Cells L2:AA15, what do '1/1440' and 'L$1+1/24' mean? and why would we need the MROUND function here?
I've tried typing other time slots like 9:30 am or 12:30 pm, but it seems like they will not be captured in the formula (maybe it's because they are not the time slots in L1:AA1?) and will not be shown as Task A/B. Instead of adding all time slots to the template (since the person may start from 8:15 am, 8:45 am etc., so there is no specific time slot), wondering if we have other ways to revise the formula?

2. For the zeroes hidden in the grey area, are there any ways to show zeroes if there are working hours shown in the normal or OT hours?
For example, if a person only works on task A for 8 hours, then Task A hour will show as 8 but Task A OT hour, Task B regular hour and Task B OT hour will all be hidden now. How can we only show those hours as zeroes and keep the rest hidden? (i.e. cell H2 and I2, J3 and K3 is in pair)

Thank you!!

Thank you JeteMc and ChemistB! Your advice really did help a lot!! I have tested JeteMc's timesheet and found that when I put 12:30pm as break start time, and 1:30pm as break end time, the regular hrs and OT hrs show as 7 and 1 respectively. However, it should be 8 regular hrs with 0 OT hour instead. Rows for weekdays have the same issue but the current formula works well for Sat and Sun.

Wondering if it's related to the SUMPRODUCT part of the formula? and any ways to resolve this issue?

and sorry that I don't understand the workflow behind this formula... wondering if you would mind explaining it in more detail? Thank you so much for all the help again!

Formula:

Please Login or Register  to view this content.

Thank you so much for your tremendous help, JeteMc!!! The template is almost perfect now!! May I ask is there any way that the template can recognize a certain weekend as a working day and calculate the working hours as per normal weekdays? (by default weekend work should be counted as OT hrs, but if it is an official working day, then it should be counted as regular hrs.)

I've thought of adding a new tab and put the official working dates into that tab. By searching and matching with the dates
I've tried revising it per below (I adjusted the cell reference, so it's not the same one) but it only works for one row. It seems like the logic is not right...

C14 is the date of the weekend
HolidayWorkingDay is the named cell range of the official working days on weekend (data list on new tab)
For other parts, they are the same as your formula.

=IF(VLOOKUP(C14,HolidayWorkingDay,1,FALSE)=C14,SUMPRODUCT((N14:CG14)*(CI14:FB14<=8)*24),IF(OR(B14="Interpretation (if any)",H14=""),"",IF(LEFT(A14)="S",0,SUMPRODUCT((N14:CG14)*(CI14:FB14<=8)*24))))

Hi JeteMc! Attached please find the revised Excel template based on yours latest version

I've added a new tab called weekend as holiday and name the cell reference as 'holidays'.

I've tried updating the formula inside cell I4 first, but it will become #N/A if the date in column C does not exist in the holiday list.

Formula:

Please Login or Register  to view this content.

For OT hours, I just leave as is first since not sure how to revise it (but I assume it will be the reversed version of column I?)

Thank you so much for the help!

Thank you so much!! The formulae work for the template!

Hi JeteMc, sorry for reverting back here but I noticed a few issues after trying out more examples, could you please look into the below and see if they can be revised? Thank you so much in advance!

Bug 1: When the working hour of the day is less than 1 hour, the formula will wrong round up to 1 hour instead of reflecting the actual hour. E.g. 9:00 am time in and 9:45 am time out, the total hour is 1 hour instead of 0.75 hour. Wondering if it's related to the formula set in M20:AB48 area?



Bug2: When the in and out times are not exactly on the hour and there's OT (e.g. in time: 9:08 PM and out time: 7:08 PM), the formula cannot accurately split the regular time as 8 and the rest as OT. Wondering if it's related columns AD:AS? since if the 8 hour cut-off point is lying in between the 2 time intervals, (e.g.7.86666, 8.866666), the sumproduct formula in column I:L can only return those hours <=8.

Thank you!!

Originally Posted by JeteMc

Hi Fish31, hope that you are doing well. I did some work on the spreadsheet to address the bugs.
Note that changes are made to formulas in columns H:K, L2:AB46 and AF2:AT15
The first bug is addressed by the yellow highlighted cells in row 6 and the second by the green highlighted cells in row 9.
Let us know if you have any questions/concerns.

Hi JeteMc, hope you are doing well as well and really thank you so much for this!!! However, the above updated template did not include your most updated formula for the weekend days (please see your reply on Apr 2 ):

column I:
=IF(OR(B4="Task B",H4=""),"",IF(AND(LEFT(A4)="S",COUNTIFS(Holiday,C4)),SUMPRODUCT((M4:AB4)*(AD4:AS4<=8))*24,IF(LEFT(A4)="S",0,SUMPRODUCT((M4:AB4)*(AD4:AS4<=8))*24)))

See if the following works for column J:
Formula: Select Code copy to clipboard
=IF(OR(B4="Task B",H4=""),"",IF(AND(ISEVEN(ROW()),LEFT(A4)="S"),H4-I4,SUMPRODUCT((M4:AB4)*(AD4:AS4<>"")*(AD4:AS4>8))*24))

Not sure if it is possible to combine merge both formulae inside columns H:K? So for normal days, we can calculate those weekend work as OT hours, but if the weekend is an official working day, then the template can calculate those weekend work per normal weekday.

Also wondering if task B formula would also be the same as task A that I can just revert back the formula range? Sorry that it is too complicated and I cannot fix it on my end..

Thank you so much for your help again!!!!!!

Hi JeteMc,

Thank you for your prompt reply as always!!!

For 'combining columns H:K', what I actually meant is to combine the formulas (calculating the in and out times on the exact hour while taking the 'weekend official working day' into consideration), not combining Task A+B regular and OT hours respectively, so the current template is actually fine! I've hidden sheet1 and sheet 3 for easier reference now.

For 'how a weekend is designated as an official working day', we would like to add a new tab named 'Weekend as holiday' and update the date in the first column. That column will be named as 'Holiday'. It is indeed implemented in your previous formula, but this formula does not return a correct total number of working hours.
=IF(OR(B4="Task B",H4=""),"",IF(AND(LEFT(A4)="S",COUNTIFS(Holiday,C4)),SUMPRODUCT((M4:AB4)*(AD4:AS4<=8))*24,IF(LEFT(A4)="S",0,SUMPRODUCT((M4:AB4)*(AD4:AS4<=8))*24)))

So I am wondering if we can combine the above formula and your latest formula together
=IF(OR(B2="Task B",G2=""),"",IF(LEFT(A2)="S",0,IF(SUM(L2:AB2)>8/24,8,SUMPRODUCT((L2:AB2)*(AD2:AT2<=8))*24)))

Please let me know if this is feasible or any info is needed, thanks a lot for your generous help again!!!

Thank you for looking into this again but seems like there are still some issues if bot tasks A and B are performed on the same day with OTs:

I've marked the errors in red in the excel, the regular hours are shown in negative values and OT hour is not correct as well.

Previously the template works well so not sure what the underlying issue is...wondering if you can help advise?

Thank you so much for helping this again!! Please feel free to let me know if anything, thank you!