Handling Time between two Dates - Varying day-offs

[DeGAUL]

Hi all. Please need an insight on how go about this.

I need to find the handling time between two dates.

An employee will get a report to process around June 7, 2011 10:17 PM and then he was able to finish processing the report by June 17, 2011 4:35 AM. The employee has shift 9:00 PM to 6:00 AM.

How will you find the handling time between June 7, 2011 10:17 PM and June 17, 2011 4:35 AM, with in mind the count should only be during his shift. Sometimes an employee can finish the report less than 24 hours and sometimes for months.

One big problem also, is that though employees have two day-offs, it does not necessarily mean they fall on Saturday and Sunday. Im thinking Networkdays wont really work here. Is there a work around for this?

Any help and insight will be greatly appreciated.

Thanks in advance.

 

[barry houdini]

Assuming Monday to Friday workdays then you can use a formula like the one I suggested here

If the two days off are always the same consecutive days then you can adjust NETWORKDAYS to cater for that......or you can use NETWORKDAYS.INTL function if you have Excel 2010

 

[grizle]

Barry is right, NETWORKDAYS can be used but its a little more complicated because that only returns integer days rather than time. The problem is further complicated because the example is a night shift through midnight.

Lets simplify it a bit though.
1. Assume the shift times are fixed.
2. Assume the start and end times are always within a worked shift
3. Assume there are 2 consecutive days off per week
4. Days off refer to the start of the shift. (He works through to 6:00 on his day off).

I believe this works correctly:
Excel 2003
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>shift start</TD><TD style="TEXT-ALIGN: right">21:00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">start</TD><TD style="TEXT-ALIGN: center">end</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">working time</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>shift end</TD><TD style="TEXT-ALIGN: right">06:00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">07/06/2011 22:17</TD><TD style="TEXT-ALIGN: right">17/06/2011 04:35</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">78.30</TD><TD>hours</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>shift hours</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>days off</TD><TD style="TEXT-ALIGN: right">1</TD><TD>sun</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD>mon</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">=$B$3*(NETWORKDAYS((E2-$B$1)-WEEKDAY(E2-$B$1)+$B$6,(F2-$B$1)-WEEKDAY(E2-$B$1)+$B$6))+24*MOD((F2-E2),1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">=MOD(24*(B2-B1+1),24)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=CHOOSE(B5,"sun","mon","tue","wed","thu","fri","sat")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=CHOOSE(B6,"sun","mon","tue","wed","thu","fri","sat")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
This works by offsetting the week back to a standard week in order to make NETWORKDAYS() work correctly and offsetting the shift start time back to midnight so that subtractions across midnight are not required.

 

[grizle]

OK my edit button has disappeared but lets correct those formulae:
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>shift start</TD><TD style="TEXT-ALIGN: right">21:00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">start</TD><TD style="TEXT-ALIGN: center">end</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">working time</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>shift end</TD><TD style="TEXT-ALIGN: right">06:00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">07/06/2011 22:17</TD><TD style="TEXT-ALIGN: right">17/06/2011 04:35</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">78.30</TD><TD>hours</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>shift hours</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>days off</TD><TD style="TEXT-ALIGN: right">1</TD><TD>sun</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD>mon</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">=$B$3*(NETWORKDAYS((E2-$B$1)-$B$6+1,(F2-$B$1)-$B$6+1))+24*MOD((F2-E2),1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">=MOD(24*(B2-B1+1),24)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=CHOOSE(B5,"sun","mon","tue","wed","thu","fri","sat")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=CHOOSE(B6,"sun","mon","tue","wed","thu","fri","sat")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[barry houdini]

Hello grizle, I misread 9:00 PM to 6:00 AM as 9:00 AM to 6:00 PM and therefore assumed that start and finish times could be outside the working hours......assuming that they can't then I think you are working on the correct lines......

....but I don't think your formula works exactly - for your example there are 7 full days + 6.3 hours so the answer should be 69.3 not 78.3.....I think you need to introduce the time adjustment into the last part of the formula too.....I think this one will give correct results given the conditions you stipulated

=B$3*(NETWORKDAYS(E2-B$1-B$6+1,F2-B$1-B$6+1)-1)+24*(MOD(F2-B$1,1)-MOD(E2-B$1,1))

Note I assume that Sunday and Monday off means that the last shift worked before the break is 21:00 on Saturday to 06:00 on Sunday and then the next shift worked will be 21:00 on Tuesday

 

[DeGAUL]

Thanks Barry, grizle. I'll try them out.

It's exactly the night shift that passes through midnight that's giving me headache. Employee starts his shift 9:00 PM Friday until 6:00 AM Saturday.

Grizle, your stipulation is basic correct except for #2. Sometimes the employee is assigned a report outside his shift. So hours should not be counted outside the shift, which complicates the matter.

So here are the basic conditions:

1. There are 3 shifts. 9PM-6AM, 12AM-9AM, 630AM-330AM.

2. Employee can be assigned a case outside his shift. But the hours should not be counted for those hours out side the shift.

3. Employee's day off are always 2 consecutive days.


I will try out the formulae and play with it and give a feedback.

Thank you, thank you, both for the help. Truly appreciated.

 

[grizle]

It's NETWORKDAYS thats not behaving as expected - its including part days. Substituting the same start and finish time results in 9 hours (1day).
Use =$B$3*(NETWORKDAYS((E2-$B$1)-$B$6+1,(F2-$B$1)-$B$6+1)-1)+24*MOD((F2-E2),1)

 

[grizle]

OK it starts to get complicated now. You have to check that the start time is within a shift period or take the start time of the shift following taking into account possible days off.
Offset the shift start back to 00:00
If offset start time later than shift length, start = day following
Check that this is a working day - WORKDAY((start +7)-5)
and correct if necessary.
Calculate NETWORKDAYS and add in (finish - start) fractional days

Please don't follow up that the report can be submitted outside working time too

=$B$3*(NETWORKDAYS(MAX(IF(MOD(E2-$B$1,1)>$B$3/24,INT(E2-$B$1+1),E2-$B$1),WORKDAY(IF(MOD(E2-$B$1,1)>$B$3/24,INT(E2-$B$1+1),E2-$B$1)+7,-5))-$B$6+1,(F2-$B$1)-$B$6+1)-1)+24*(MOD(F2-$B$1,1)-MOD(MAX(IF(MOD(E2-$B$1,1)>$B$3/24,INT(E2-$B$1+1),E2-$B$1),WORKDAY(IF(MOD(E2-$B$1,1)>$B$3/24,INT(E2-$B$1+1),E2-$B$1)+7,-5)),1))

 

[barry houdini]

Hello grizle/DeGAUL,

If you take the formula I linked to initially that will accommodate start and finish times outside the shift periods, adjusted to allow only start time outside business hours.....and to allow shifts crossing midnight and variable rest days that would look like this:

=(NETWORKDAYS(E2-B$6-B$1+1,F2-B$6-B$1+1)-1)*B$3+MOD(F2-B$1,1)*24-NETWORKDAYS(E2-B$6-B$1+1,E2-B$6-B$1+1)*MIN(MOD(E2-B$1,1)*24,B$3)

In many cases that gives me the same results as your last suggestion, grizle.....but there are still some discrepancies. For example with rest days Sun and Mon and shift 21:00 to 06:00 and E2 23/07/2011 22:00:00, F2 exactly 5 days later, i.e. 28/07/2011 22:00:00 I suggest that the result should be 27.00 but your formula gives 19.00

 

[grizle]

Excellent Barry. Not sure where mine went wrong but I'm still finding a slight glitch with yours when the start time = shift start (21:00). MOD(E2-B$1,1) is returning 0.99 recurring rather than 0. Something of a fiddle but adding an additional millisecond or two forces the correct result with negligible error.

=(NETWORKDAYS(E2-B$6-B$1+1,F2-B$6-B$1+1)-1)*B$3+MOD(F2-B$1,1)*24-NETWORKDAYS(E2-B$6-B$1+1,E2-B$6-B$1+1)*MIN(MOD(E2+10^-5-B$1,1)*24,B$3)