Calculating Overtime

[ceverett]

Hi, I have a service desk application that dumps raw data into a .csv file for analysis. While I am able to do most things I am stumped on working out the business hours and overtime hours. Business hours start at 8:00am and end 5:30pm Monday to Friday, everything outside of that time is classed as overtime. The application is ticket based and so the report dumps the times for each job rather than times for each engineer, this means that there are multiple entries per day per engineer.

The .csv file has the following entries, I have summarised and removed all columns apart from what is relevant (date is start date of the job as they can go over midnight). I am on Windows 8.1 running Office + 2013. Thanks in advance.

[TABLE="width: 260"]
<tbody>[TR]
[TD]K[/TD]
[TD]N[/TD]
[TD]S[/TD]
[TD]AU[/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD]finish[/TD]
[TD]hours[/TD]
[TD]start[/TD]
[/TR]
[TR]
[TD="align: right"]2/01/2015[/TD]
[TD="align: right"]16:06[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]8:30[/TD]
[/TR]
[TR]
[TD="align: right"]3/01/2015[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]18:00[/TD]
[/TR]
[TR]
[TD="align: right"]3/01/2015[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]22:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Edit the formula to your data layout or experiment with this layout
Date A2, Start Time B2, End Time C2, Regular E2
Business hours start 8:00 named Start
Buiness hours end 5:30 pm named End

Regular hours
=(WEEKDAY(A2,2)<6)*((MIN(C2,End)-MAX(B2,Start))+(B2>C2))*24

 

[ceverett]

Thanks Dave, unfortunately something isn't correct, business hours still appear and out of hours have incorrect amounts and weekends show as 0. See below for some sample data (3rd & 4th is a weekend).

[TABLE="width: 260"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]start[/TD]
[TD]finish[/TD]
[TD]OT[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2015[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2015[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2015[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"]19:30[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2015[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2015[/TD]
[TD="align: right"]14:45[/TD]
[TD="align: right"]16:25[/TD]
[TD="align: right"]1.666667[/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2015[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]19:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2015[/TD]
[TD="align: right"]19:30[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2015[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Copan1795]

in g3-g10 put
=(C12-B12)+(E12-D12)

in H3-H10 you could= G3 - 8 that would be OT
A[TABLE="width: 497"]
<colgroup><col style="width: 72pt; mso-width-source: userset; mso-width-alt: 3328;" span="6" width="95"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3025;" width="87"> <tbody>[TR]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 95, bgcolor: transparent"]Start of work[/TD]
[TD="class: xl86, width: 190, bgcolor: transparent, colspan: 2"]Lunch break in and out, military time, ex: 12:03, 13:30[/TD]
[TD="class: xl64, width: 95, bgcolor: transparent"]End of work[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 87, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Military time[/TD]
[TD="class: xl65, bgcolor: transparent"]Military time[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]G[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]ex: 00:00[/TD]
[TD="class: xl66, bgcolor: transparent"]ex: 00:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: gray"]Day[/TD]
[TD="class: xl68, bgcolor: gray"]In[/TD]
[TD="class: xl67, bgcolor: gray"]Out[/TD]
[TD="class: xl67, bgcolor: gray"]In[/TD]
[TD="class: xl67, bgcolor: gray"]Out[/TD]
[TD="class: xl67, bgcolor: gray"]Vacation[/TD]
[TD="class: xl67, bgcolor: gray"]Total[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Sunday[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0:00[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Monday[/TD]
[TD="class: xl85, bgcolor: transparent"]3/23/2014 19:45[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]3/24/2014 9:45[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]14:00[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Tuesday[/TD]
[TD="class: xl85, bgcolor: transparent"]3/24/2014 21:00[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]3/25/2014 10:00[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]13:00[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Wednesday[/TD]
[TD="class: xl85, bgcolor: transparent"]3/25/2014 21:15[/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]3/26/2014 10:30[/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]13:15[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Thursday[/TD]
[TD="class: xl85, bgcolor: transparent"]3/26/2014 21:00[/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]3/27/2014 10:00[/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]13:00[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Friday[/TD]
[TD="class: xl85, bgcolor: transparent"]3/27/2014 21:15[/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]3/28/2014 8:00[/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]10:45[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: silver"]Saturday[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0:00[/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent, colspan: 2"]Employee signature:[/TD]
[TD="class: xl79, bgcolor: transparent"]Date:[/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]TOTAL HOURS[/TD]
[TD="class: xl83, bgcolor: transparent, align: right"]64:00:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

The formula that I suggested is to calculate the regular hours.

Regular hours
=(WEEKDAY(A2,2)<6)*((MIN(C2,End)-MAX(B2,Start))+(B2>C2))*24

Similar logic can be used to calculate the overtime.

 

[Dave Patton]

The formula that I suggested is to calculate the regular hours.

Does Total hours - Regular Hours = OT Hours?

or you can use logic similar to the Reg Time calculation to calculate the Overtime.

If do not already have total hours, consider =((C2-B2)+(B2>C2))*24

 

[ceverett]

Thanks Dave, yes, total hours - regular hours = OT hours. So I can calculate both regular, total and OT hours now, thanks! The only issue now is when someone works past midnight as it shows as all regular hours. I can work with this though as I'm not sure there will be an easy answer for this one...

 

[ceverett]

Thanks Copan1795 but my timesheet entries are task based and will have multiple entries per person per day. Your calcs work for timesheet entries.

 

[ceverett]

Hi Dave, just found one more issue, OT on Friday evenings show up strange, if you enter the following data it shows as a negative Regular hours.

2/10/2015 19:30 20:00

 

[Dave Patton]

The calculation show 0.5 hours total hours, regular time, and 0.5 OT.

Check the formula that you used and review the logic of the suggestions.