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]

 

[ceverett]

I've triple checked and even changed another working entry to a Friday with similar times and that has issues as well. Note that I'm in Australia and 2/10 is Friday the 2nd of October.
Date Start End Business Hrs Total Hrs After hours
[TABLE="width: 388"]
<colgroup><col width="68"><col span="5" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68, align: right"]2/10/2015
[/TD]
[TD="class: xl66, width: 64, align: right"]19:30[/TD]
[TD="class: xl66, width: 64, align: right"]20:00[/TD]
[TD="width: 64, align: right"]-2[/TD]
[TD="width: 64, align: right"]0.5[/TD]
[TD="width: 64, align: right"]2.5
[/TD]
[/TR]
</tbody>[/TABLE]

Business Hrs '=(WEEKDAY(A2,2)<6)*((MIN(C2,End)-MAX(B2,Start))+(B2>C2))*24'
Total Hrs '=((C2-B2)+(B2>C2))*24'
After Hrs '=E2-D2'

 

[Dave Patton]

Please test the following.

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

 

[ceverett]

I get a #NAME? error. I had to add an open bracket to fix a formula error.
=AND(WEEKDAY(A2,2)<6,(B2C2))*24+AND(B2>C2,C2>Start)*(C2-Start)*24


-- removed inline image ---

 

[ceverett]

Sorry, my screen capture didn't do so well! It was the evaluate formula

=AND(TRUE,(B2C2))*24+AND(B2>C2,C2>Start)*(C2-Start)*24

 

[ceverett]

Changing the formula to be =AND(WEEKDAY(A2,2)<6,(B2,C2))*24+AND(B2>C2,C2>Start)*(C2-Start)*24
Gives me the following
[TABLE="width: 388"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]start
[/TD]
[TD]finish
[/TD]
[TD]BH[/TD]
[TD]TH[/TD]
[TD]AH[/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"]24[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-21[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

The formula got messed up with the post.

I think a new approach is required.

I hope that someone can help you. I must sign off for the night.

Dave

 

[NinadM]

Hi,

You could try the below formula which should work on all possible scenarios.

[TABLE="width: 440"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Business Hours[/TD]
[TD]After Hours[/TD]
[/TR]
[TR]
[TD]02-10-2015[/TD]
[TD]08:00[/TD]
[TD]16:06[/TD]
[TD]08:06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-10-2015[/TD]
[TD]13:30[/TD]
[TD]16:30[/TD]
[TD][/TD]
[TD]03:00[/TD]
[/TR]
[TR]
[TD]03-10-2015[/TD]
[TD]16:30[/TD]
[TD]19:30[/TD]
[TD][/TD]
[TD]03:00[/TD]
[/TR]
[TR]
[TD]04-10-2015[/TD]
[TD]13:00[/TD]
[TD]13:30[/TD]
[TD][/TD]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]05-10-2015[/TD]
[TD]14:45[/TD]
[TD]16:25[/TD]
[TD]01:40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06-10-2015[/TD]
[TD]19:00[/TD]
[TD]19:30[/TD]
[TD][/TD]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]07-10-2015[/TD]
[TD]19:30[/TD]
[TD]00:30[/TD]
[TD][/TD]
[TD]05:00[/TD]
[/TR]
[TR]
[TD]08-10-2015[/TD]
[TD]00:30[/TD]
[TD]01:00[/TD]
[TD][/TD]
[TD]00:30[/TD]
[/TR]
</tbody>[/TABLE]

Business Hours

=IF(WEEKDAY($A2,2)<6,MOD(IF(AND(TEXT($B2,"hh:mm")<text(timevalue("17:30"),"hh:mm"),text($c2,"hh:mm")>TEXT(TIMEVALUE("08:00"),"hh:mm")),MIN(TEXT($C2,"hh:mm"),TEXT(TIMEVALUE("17:30"),"hh:mm")),VALUE(TIMEVALUE("00:00")))-IF(AND(TEXT($B2,"hh:mm")<text(timevalue("17:30"),"hh:mm"),text($c2,"hh:mm")>TEXT(TIMEVALUE("08:00"),"hh:mm")),MAX(TEXT($B2,"hh:mm"),TEXT(TIMEVALUE("08:00"),"hh:mm")),VALUE(TIMEVALUE("00:00"))),1),VALUE(TIMEVALUE("00:00")))<text(timevalue("17:30"),"hh:mm"),text(c2,"hh:mm")><text(timevalue("17:30"),"hh:mm"),text(c2,"hh:mm")>

After Hours

=MOD($C2-$B2,1)-$D2
</text(timevalue("17:30"),"hh:mm"),text(c2,"hh:mm")></text(timevalue("17:30"),"hh:mm"),text(c2,"hh:mm")></text(timevalue("17:30"),"hh:mm"),text($c2,"hh:mm")></text(timevalue("17:30"),"hh:mm"),text($c2,"hh:mm")>