• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to automate a timesheet template?

fish31

New Member
Hi all!
I’ve got a billing template and would like automate the calculation of hours as much as possible, wondering if anyone can help take a look?
Post image
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)?

Here are the formulae I’ve got in hand, thank you!

Regular hours:
=IF(24*(IF([@[Time In]]>[@[Break Start]],[@[Break Start]]+1-[@[Time In]],[@[Break Start]]-[@[Time In]])+IF([@[Break End]]>[@[Time Out]],[@[Time Out]]+1-[@[Break End]],[@[Time Out]]-[@[Break End]]))>8,8,24*(IF([@[Time In]]>[@[Break Start]],[@[Break Start]]+1-[@[Time In]],[@[Break Start]]-[@[Time In]])+IF([@[Break End]]>[@[Time Out]],[@[Time Out]]+1-[@[Break End]],[@[Time Out]]-[@[Break End]])))
OT hours:
=IF(24*(IF([@[Time In]]>[@[Break Start]],[@[Break Start]]+1-[@[Time In]],[@[Break Start]]-[@[Time In]])+IF([@[Break End]]>[@[Time Out]],[@[Time Out]]+1-[@[Break End]],[@[Time Out]]-[@[Break End]]))>8,24*(IF([@[Time In]]>[@[Break Start]],[@[Break Start]]+1-[@[Time In]],[@[Break Start]]-[@[Time In]])+IF([@[Break End]]>[@[Time Out]],[@[Time Out]]+1-[@[Break End]],[@[Time Out]]-[@[Break End]]))-8,0)

Thank you!!
 

Attachments

  • Sample timesheet.xlsx
    10.4 KB · Views: 5
Last edited:
fish31
Seems that You have visited in Forum Rules
... but ... could You follow those rules too?

You have Cross-Posted other Your threads too.
eg https://chandoo.org/forum/threads/how-to-use-the-trendline-to-find-percentage-change.43693/

You should read this https://www.excelguru.ca/content.php?184 too.

fish31 ... continues
As You're still new as You wrote in this Forum, You've just read those Forum Rules before start to writing any threads.
... then You should remember those (Forum Rules) and if You don't, then You could always reread those again - if needed.
I asked: could You follow those rules too?


for pecoflyer

Chandoo.org Forum Rules tells, what and how should work in Chandoo.org Forum - not others.
... and all which has NOT forbidden are not always good to do.
 
Last edited:
fish31
Seems that You have visited in Forum Rules
... but ... could You follow those rules too?

You have Cross-Posted other Your threads too.
eg https://chandoo.org/forum/threads/how-to-use-the-trendline-to-find-percentage-change.43693/

You should read this https://www.excelguru.ca/content.php?184 too.

Hi vletm! Sorry I'm still new to the forum and thank you for letting me know this! :) will take note of this and avoid cross-posting! Again sorry for the inconvenience brought!
 
Please note that cross posting is NOT forbidden ( nor here nor on other forums) , as long as you add links to your cross posts.
It is clearly explained on the link provided in post #3 to excelguru.ca
 
Back
Top