Using Microsoft Excel 2013 to Calculate Work Hours

Question

I am trying to develop a table that will calculate our techs' response times.

In column C is when the call was received and in column F is when the tech responded. Columns D and E are to be subtracted from the overall response time (since techs will not be penalized for parts ordering). Note that not every call requires a parts order, in which case columns D and E will be blank. I would like the table to exclude weekends and non-business hours (and lunch breaks are excluded from response time). If a call arrives outside of business hours or during lunch, it is entered as happening at the time business next resumes.

I have been trying to understand this page but am unable to do so.

column A: customer
Column B: WO#
Column C: call received (mm/dd/yy hh:mm am/pm)
column D: parts ordered (mm/dd/yy hh:mm am/pm)
column E: parts received (mm/dd/yy hh:mm am/pm)
column F: dispatch time (mm/dd/yy hh:mm am/pm)
column G: response time (hh:mm) 
column H: response time (converted to fractional hours i.e. 2:15 would display in this column as 2.25)

business hours (j2: 8:00 am) (j3: 5:00 pm)
lunch break (j5: 12:00 pm)
lunch break end (j6: 1:00 pm)

All dates and times are entered as Excel date/time values. The result I'm looking for is the response time. The same value will appear in columns G and H, but formatted differently.

Answer 1

This problem has a huge number of possible combinations:

• when calls arrive relative to the workweek, workday and lunch
• whether or not parts are required
• when the parts request is placed relative to the workweek, workday, lunch, and the call arrival
• whether parts arrival spans a weekend or even several weekends if they are not available
• whether tech dispatch vs. call arrival and/or tech dispatch vs. parts arrival spans a weekend
• all possible combinations and permutations of the above.

I couldn't see a way to use a generic formula to just count things. It looks like for each response scenario, you need to model exactly when each activity happens relative to the workweek, workday, and lunch, and add and subtract relevant times. The rules get very complex, and this is without dealing with holidays, which would add an additional dimension.

It is technically possible to do this with one "formula", but it would be so long and complex that you would never be able to troubleshoot or modify it, or make sense of it after the fact. So this answer will use a bunch of building block columns (some of which contain some long formulas), and these get mixed and matched and reused. It will provide a basis for modifying things.

I created the most extreme, convoluted scenario I could think of to test it. It's possible that this solution is overly complicated because there are some situations that don't occur in real life. You're welcome to simplify it.

The building block columns

Days & Times
Excel stores date/times as a single number. The whole number portion is a day count and the fractional portion is the time as a decimal fraction of a day. Various parts of the formulas need to use just days or just times, so the first eight building blocks split those out. Columns K through N are the days for columns C through F and columns O through R are the times. It doesn't make much difference how you format these columns. The formulas will yield numbers (the days will be numbers just shy of 42000, times will be decimal fractions--noon or 12 hours will be 0.5). Assuming your data starts in row 2:

K2:  =INT(C2)
L2:  =INT(D2)
M2:  =INT(E2)
N2:  =INT(F2)
O2:  =MOD(C2,1)
P2:  =MOD(D2,1)
Q2:  =MOD(E2,1)
R2:  =MOD(F2,1)

Raw Durations and Parts Ordering Time
There are several ways to handle the parts ordering time. One is to calculate the duration and subtract it out. In this problem, that adds another complexity because you don't want to double-count exclusions for lunch or weekends. I used a different approach, breaking the problem into two pieces in cases where parts are ordered--what happens up to ordering the parts and what happens after the parts are received; all time in between being ignored.

This requires three durations:

• call receipt to parts ordering
• parts receipt to tech dispatch
• call receipt to tech dispatch for cases where no parts are required

Some formulas require the actual elapsed time, others require day counts irrespective of time of day, so there are six building block columns. These durations subtract out any weekends. I assumed that a weekend could possibly occur between call receipt and placing a parts order (call arrives at the end of the day on Friday), or between parts receipt at the end of a Friday and tech dispatch on Monday. Columns S through U are the actual elapsed times. Columns V through X are the day counts.

S2:  =D2-C2-IF(WEEKDAY(C2,2)+L2-K2>5,2,0)
T2:  =F2-E2-IF(N2-M2+WEEKDAY(E2,2)>5,2,0)
U2:  =F2-C2-INT((N2-K2+WEEKDAY(C2,2))/7)*2
V2:  =L2-K2-IF(WEEKDAY(C2,2)+L2-K2>5,2,0)
W2:  =N2-M2-IF(N2-M2+WEEKDAY(E2,2)>5,2,0)
X2:  =N2-K2-INT((N2-K2+WEEKDAY(C2,2))/7)*2

Note that a different adjustment for weekends is used for the "no-parts" case. This is because my extreme test case included a weekend between call arrival and parts ordering, a long delay for parts arrival, and another weekend between parts arrival and tech dispatch. For the "no-parts" scenario, I just used the entire time, which included two weekends, so the formula needed to handle that. If you would fire any tech who required two weeks to respond to a call, you can change that to the same type of single-weekend adjustment used in the other formulas if you want consistency.

Qualifying Times
The next step is to deal with workday timeframes. I broke the problem into three intervals.

• First Day (relevant times start at call arrival and can have various end points)
• Last Day (relevant times can have various start points and end with tech dispatch)
• Days Between (these are all whole days and need adjustment for weekends)

All activities could happen in a single day or two days, so the formulas need to test for whether the specific interval exists and has not already been accounted for. The formulas are different depending on whether parts ordering is required, so there are two formulas for each interval (parts-required is the first in each set).

First Day
Y2:  =IF(L2=K2,IF(M2=K2,IF(N2=K2,R2-Q2+P2,$J$3-Q2+P2),P2),$J$3)-O2
Z2:  =IF(N2=K2,R2,$J$3)-O2

Last Day
AA2:  =IF(M2=N2,IF(L2=N2,IF(K2=N2,0,P2-$J$2+R2-Q2),R2-Q2),R2-$J$2)
AB2:  =IF(K2=N2,0,R2-$J$2)

Days Between
AC2:  =IF(L2=K2,0,IF(M2=L2,IF(N2=L2,0,$J$3-Q2+P2-$J$2+(L2-K2-1-INT((L2-K2+WEEKDAY(C2,2))/7)*2)*
       ($J$3-$J$2)),P2-$J$2+(L2-K2-1-INT((L2-K2+WEEKDAY(C2,2))/7)*2)*($J$3-$J$2)))+
       IF(M2=L2,0,IF(N2=M2,0,$J$3-Q2+(N2-M2-1-INT((N2-M2+WEEKDAY(E2,2))/7)*2)*($J$3-$J$2)))
AD2:  =IF(N2-K2>1,N2-K2-1-INT((N2-K2+WEEKDAY(C2,2))/7)*2,0)*($J$3-$J$2)

Note that I split the long formula for readability. If you want to copy and paste, you will need to delete the carriage returns and extra spaces.

Lunch Breaks
The lunch break adjustments determine which days have applicable lunch breaks, counts them, and multiplies the count by the stored lunch break spec. Again, the parts-required case is first:

AE2:  =(IF(S2>$J$5-O2,1,0)+IF(S2>P2-$J$6,1,0)+IF(V2>=2,V2-1,0)+IF(T2>$J$5-Q2,1,0)+IF(T2>R2-$J$6,1,0)+IF(W2>=2,W2-1,0))*($J$6-$J$5)
AF2:  =(IF(U2>$J$5-O2,1,0)+IF(U2>R2-$J$6,1,0)+IF(X2>=2,X2-1,0))*($J$6-$J$5)

Tech Response Time
The response time is then selecting the appropriate set of numbers to combine (whether or not parts are required), and combining the pieces. Cell G2:

G2:  =IF(ISBLANK(D2),Z2+AB2+AD2-AF2,Y2+AA2+AC2-AE2)

Use a custom format. Under formats, select a time format that is close and then customize it in the customization window. You can use something like hh:mm or hh"h "mm"m", which would produce a result like 03h 47m.

In Column H, you want hours and decimal hours. I don't do a lot of time formatting in Excel, but it seems like the built-in formats like to deal with only whole hours. If I'm wrong, perhaps someone else can edit this answer with a better formatting approach. I would just convert the stored number to hours and then format it as a number with the desired number of decimal places:

H2:  =G2*24

This converts decimal fraction of a day to hours.

You can copy this row of formulas down the page as needed. If you don't want to look at the building block columns, hide those columns.