Net Work Times

I couldn't have pulled this off without Chip Pearson's formula on work days and times. In a nutshell, when I was still at Pearson, I was asked to figure out how much time had elapsed between the creation of a ticket, and the time a support agent actually started work on that ticket.

This proved to be one of the ugliest formulas I ever wrote. It was two parts:

1. Figure out the earliest time that someone could have touched the ticket
2. Figure out when someone actually did, and how many "work hours" had elapsed between the opening of the ticket and that time

I started by creating named ranges:

The first was the days of the week, the day the ticket could have been opened, days elapsed between those two, and the respective start and end times, called "dayStart."

Day Email Came In Day Service Starts # of Days Start Hour End Hour
Sunday Monday 1 4:00 AM 5:00 PM
Monday Monday 0 5:00 PM
Tuesday Tuesday 0 5:00 PM
Wednesday Wednesday 0 5:00 PM
Thursday Thursday 0 5:00 PM
Friday Friday 0 5:00 PM
Saturday Monday 2 4:00 AM 5:00 PM

The second named range was the list of holidays for the year in question (2006-2007), called "holidayList."

The third and fourth named ranges were just the standard start and end times for the business ("startTime" and "endTime", respectively.)

A typical row of data in the spreadsheet began with the case number, and the date/time opened (columns A and B):

Day Email Came In
Sunday 00:00 00:00
Monday 04:00 17:00
Tuesday 04:00 17:00
Wednesday 04:00 17:00
Thursday 04:00 17:00
Friday 04:00 17:00
Saturday 00:00 00:00

I had the week # and year in columns C and D; in E, the first of two formulas:
=IF(AND(DAY(B2)>=2,DAY(B2)<=5,HOUR(B2)>4),text(date(year(b2),month(b2),day(b2)),"mm/dd/yyyy")&>=17,TEXT(B2,"dddd")="Friday"),TEXT(DATE(YEAR(B2),MONTH(B2),DAY(B2)+3),"mm/dd/yy"

In column F, I had the ticket touched time; in G, the Actual Duration:
=(IF(B2=F2,0,IF(AND(INT(E2)=INT(F2),NOT(ISNA(MATCH(INT(E2),holidayList,0)))),0,ABS(IF(INT(E2)=INT(F2),24*(F2-E2),(24*(endTime-startTime)*(MAX(NETWORKDAYS(E2+1,F2-1,holidayList),0)+INT(24*(((F2-INT(F2))-(E2-INT(E2)))+(endTime-startTime))/(24*(endTime-startTime))))+MOD(ROUND(((24*(F2-INT(F2)))-24*startTime)+(24*endTime-(24*(E2-INT(E2)))),2),ROUND((24*(endTime-startTime)),2)))))))/24)

YIKES.

Yeah, well, it worked.

If this code can be of any use to anyone, please do? A former colleague of mine (Jeff Z) mentioned on my Facebook page that he gets questions about this in class - here you go, my friend. (For those of you who live in the Sacramento area, Jeff Z is a former colleague of mine from New Horizons Computer Learning Center. While I was Training Supervisor there, I once had the honor of awarding him Instructor of the Year, where I described him as "more the All-Madden Team than the Heisman, but an MVP on any team.")

Thank you to Chip Pearson, for making this formula possible, and to the support management team at Pearson (no relation) for giving me the chance to make this happen. Be well.