How to automate a timesheet template?

%3CLINGO-SUB%20id%3D%22lingo-sub-1201124%22%20slang%3D%22en-US%22%3EHow%20to%20automate%20a%20timesheet%20template%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1201124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20all!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%E2%80%99ve%20got%20a%20billing%20template%20and%20would%20like%20automate%20the%20calculation%20of%20hours%20as%20much%20as%20possible%2C%20wondering%20if%20anyone%20can%20help%20take%20a%20look%3F%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-02-29%20at%201.25.23%20am.png%22%20style%3D%22width%3A%20586px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F174118iAB8669F954CDE9C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screen%20Shot%202020-02-29%20at%201.25.23%20am.png%22%20alt%3D%22Screen%20Shot%202020-02-29%20at%201.25.23%20am.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20grey%20area%20is%20where%20should%20be%20calculated%20by%20formula%20and%20it%20will%20be%20calculated%20based%20on%20the%20hours%20spent%20on%20task%20A%20and%20B%20within%20a%20day.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3ETotal%20regular%20hours%20is%208%20hours%20for%20a%20day.%20After%208%20hours%2C%20no%20matter%20what%20task%20I%E2%80%99m%20performing%2C%20all%20OT%20hours%20will%20fall%20to%20that%20particular%20task.%20For%20example%2C%20I%20worked%20on%20Task%20A%20from%209%20am-12%20pm%2C%201%20pm-6%20pm%2C%20and%20then%20had%20overtime%20work%20on%20Task%20B%20from%206-7pm%2C%20then%20my%20OT%20hour%20would%20fall%20to%20Task%20B%20(1%20Hour)%2C%20and%20if%20I%20worked%20on%20Task%20A%20again%20from%207-8pm%2C%20then%20there%20would%20be%201%20hour%20OT%20for%20task%20A%20as%20well.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20got%20the%20basic%20formula%20of%20calculating%20normal%20hour%20and%20OT%20hour%20per%20task%2C%20but%20I%20cannot%20figure%20out%20how%20to%20calculate%20the%20total%20hours%20based%20on%20the%20timeline%20of%20both%20tasks.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EAnd%20for%20Saturday%20and%20Sunday%2C%20it%20is%20by%20default%20calculated%20as%20overtime%20hrs%20but%20sometimes%20it%20may%20be%20counted%20as%20regular%20hours%20if%20that%20day%20is%20an%20official%20working%20day.%20I%E2%80%99ve%20searched%20online%20and%20found%20out%20we%20can%20add%20a%20button%20(or%20any%20other%20better%20methods!%20J)%20to%20be%20clicked%20on%20to%20calculate%20base%20on%20different%20rate.%20Wondering%20if%20that%E2%80%99s%20possible%20to%20do%20this%20on%20the%20template%20(though%20it%E2%80%99s%20for%20the%20regular%2FOT%20hrs)%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EHere%20are%20the%20formulae%20I%E2%80%99ve%20got%20in%20hand%2C%20thank%20you!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ERegular%20hours%3A%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3E%3DIF(24*(IF(%5B%40%5BTime%20In%5D%5D%26gt%3B%5B%40%5BBreak%20Start%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D%2B1-%5B%40%5BTime%20In%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D-%5B%40%5BTime%20In%5D%5D)%2BIF(%5B%40%5BBreak%20End%5D%5D%26gt%3B%5B%40%5BTime%20Out%5D%5D%2C%5B%40%5BTime%20Out%5D%5D%2B1-%5B%40%5BBreak%20End%5D%5D%2C%5B%40%5BTime%20Out%5D%5D-%5B%40%5BBreak%20End%5D%5D))%26gt%3B8%2C8%2C24*(IF(%5B%40%5BTime%20In%5D%5D%26gt%3B%5B%40%5BBreak%20Start%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D%2B1-%5B%40%5BTime%20In%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D-%5B%40%5BTime%20In%5D%5D)%2BIF(%5B%40%5BBreak%20End%5D%5D%26gt%3B%5B%40%5BTime%20Out%5D%5D%2C%5B%40%5BTime%20Out%5D%5D%2B1-%5B%40%5BBreak%20End%5D%5D%2C%5B%40%5BTime%20Out%5D%5D-%5B%40%5BBreak%20End%5D%5D)))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSTRONG%3EOT%20hours%3A%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3E%3DIF(24*(IF(%5B%40%5BTime%20In%5D%5D%26gt%3B%5B%40%5BBreak%20Start%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D%2B1-%5B%40%5BTime%20In%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D-%5B%40%5BTime%20In%5D%5D)%2BIF(%5B%40%5BBreak%20End%5D%5D%26gt%3B%5B%40%5BTime%20Out%5D%5D%2C%5B%40%5BTime%20Out%5D%5D%2B1-%5B%40%5BBreak%20End%5D%5D%2C%5B%40%5BTime%20Out%5D%5D-%5B%40%5BBreak%20End%5D%5D))%26gt%3B8%2C24*(IF(%5B%40%5BTime%20In%5D%5D%26gt%3B%5B%40%5BBreak%20Start%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D%2B1-%5B%40%5BTime%20In%5D%5D%2C%5B%40%5BBreak%20Start%5D%5D-%5B%40%5BTime%20In%5D%5D)%2BIF(%5B%40%5BBreak%20End%5D%5D%26gt%3B%5B%40%5BTime%20Out%5D%5D%2C%5B%40%5BTime%20Out%5D%5D%2B1-%5B%40%5BBreak%20End%5D%5D%2C%5B%40%5BTime%20Out%5D%5D-%5B%40%5BBreak%20End%5D%5D))-8%2C0)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you!!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1201124%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1201191%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automate%20a%20timesheet%20template%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1201191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565986%22%20target%3D%22_blank%22%3E%40fishfish31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20considered%20using%20MS%20Project%3F%26nbsp%3B%20If%20you%20have%20access%20to%20it%20then%20it's%20worth%20a%20look.%26nbsp%3B%20It%20has%20a%20lot%20of%20functionalities%20you%20describe%20here.%26nbsp%3B%20In%20Excel%20you'd%20be%20building%20the%20engine%2C%20so%20to%20speak.%26nbsp%3B%20Project%20does%20these%20things%20out%20of%20the%20box.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1202343%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automate%20a%20timesheet%20template%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1202343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20reply!%20No%20I%20don't%20think%20I%20have%20access%20to%20MS%20Project...You've%20mentioned%20building%20the%20engine%20so%20that%20means%20we%20cannot%20use%20a%20complex%20formula%20to%20automatically%20calculate%20the%20hours%20right%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1202540%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automate%20a%20timesheet%20template%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1202540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565986%22%20target%3D%22_blank%22%3E%40fishfish31%3C%2FA%3E%26nbsp%3BIf%20you%20need%20to%20do%20this%20in%20Excel%2C%20you%20can%20achieve%20what%20you%20want%20with%20a%20slightly%20different%20set-up%20of%20your%20time%20card.%20I%20demonstrate%20this%20in%20the%20attached%20file%20below%20yours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20is%20that%20you%20look%20at%20In%2C%20Start%20break%2C%20End%20break%20and%20Out%20for%20each%20task%20separately.%20For%20instance%2C%20on%20Friday%2C%20Task%20A%20started%209AM%20with%20a%20break%20from%2012PM%20until%206PM%20and%20then%20one%20more%20hour%20until%207PM.%20Task%20B%20started%201PM%20with%20no%20breaks%20to%20end%206PM.%26nbsp%3BThe%20Over-time%20formula%20for%20weekdays%20are%20not%20straight-forward%2C%20though%2C%20I%20believe%20that%20they%20are%20easier%20to%20comprehend%20than%20what%20you%20described%20above.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20standard%20week-end%20formula%20for%20over-time%20is%20far%20more%20easy.%20I%20added%20an%20entry%20for%20Saturday%20to%20test%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1202828%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automate%20a%20timesheet%20template%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1202828%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20take%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B's%20example%20and%20take%20it%20a%20bit%20further.%20I%20prefer%20to%20separate%20form%20from%20function%20in%20a%20case%20like%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20mean%20by%20that%20is%20make%20the%20data%20collection%2Finput%20as%20straight%20forward%20as%20possible%20by%20just%20entering%20new%20records%20on%20a%20table.%20Perform%20your%20calculations%20on%20the%20table%20(function)%2C%20and%20present%20the%20information%20however%20you%20want%20(form).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20for%20example%20of%20what%20I%20mean%20by%20that%20(forgive%20total%20lack%20of%20formatting%20finesse).%20In%20this%20case%2C%20you%20would%20just%20capture%20the%20person%2C%20date%2C%20task%2C%20and%20time%20in%2Fout%20on%20one%20record.%20In%20the%20example%2C%20the%20order%20DOES%20matter.%20You%20can%20have%20different%20people%20entering%20their%20time%20in%20between%2C%20but%20the%20entries%20for%20any%20given%20person%20must%20be%20chronological.%20This%20also%20does%20not%20incorporate%20any%20safety%20for%20things%20like%20timestamps%20that%20overlap%20or%20are%20backwards.%20You%20would%20want%20to%20use%20validation%20or%20something%20similar%20to%20prevent%20bad%20data%20entry.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

 

Screen Shot 2020-02-29 at 1.25.23 am.png


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

4 Replies

@fishfish31 

Have you considered using MS Project?  If you have access to it then it's worth a look.  It has a lot of functionalities you describe here.  In Excel you'd be building the engine, so to speak.  Project does these things out of the box.

@Patrick2788 Thanks for the reply! No I don't think I have access to MS Project...You've mentioned building the engine so that means we cannot use a complex formula to automatically calculate the hours right?

@fishfish31 If you need to do this in Excel, you can achieve what you want with a slightly different set-up of your time card. I demonstrate this in the attached file below yours.

 

The idea is that you look at In, Start break, End break and Out for each task separately. For instance, on Friday, Task A started 9AM with a break from 12PM until 6PM and then one more hour until 7PM. Task B started 1PM with no breaks to end 6PM. The Over-time formula for weekdays are not straight-forward, though, I believe that they are easier to comprehend than what you described above. 

The standard week-end formula for over-time is far more easy. I added an entry for Saturday to test it.

 

I would take @Riny_van_Eekelen 's example and take it a bit further. I prefer to separate form from function in a case like this. 

 

What I mean by that is make the data collection/input as straight forward as possible by just entering new records on a table. Perform your calculations on the table (function), and present the information however you want (form).

 

See attached for example of what I mean by that (forgive total lack of formatting finesse). In this case, you would just capture the person, date, task, and time in/out on one record. In the example, the order DOES matter. You can have different people entering their time in between, but the entries for any given person must be chronological. This also does not incorporate any safety for things like timestamps that overlap or are backwards. You would want to use validation or something similar to prevent bad data entry.