Calculating between days/times

[scottishmovies]

Hi All,

I want to calculate against two dates/times to measure response times.

I need to be able to calculate against the work day (exclude Weekends) and the working time (exclude times outwith working) and subtract one from the other to arrive at a severity level. This is to measure if we have acheived a proper timely response. Is this feasible in Excel? What is the formula I needd to put in Col D to make this work?

Hope someone can help as it's driving me nuts!

Thanks in advance,
Pat

Excel Workbook
	A	B	C	D	E	F
1
2		Work Days = Mon-Fri	Work Times = 08:30 - 17:30
3
4		Severity Level
5		A	<4 hours
6		B	> 4 hours<8 hours
7		C	> 8 hours< 1 week
8		D	> 1 week
9
10		Date 1	Date 2	Expected response
11	1	01/03/2011 12:00	01/03/2011 15:15	A
12	2	01/03/2011 12:00	01/03/2011 16:15	B
13	3	01/03/2011 12:00	02/03/2011 14:00	C
14	4	01/03/2011 12:00	07/03/2011 12:00	D
15	5	05/03/2011 16:00	07/03/2011 09:00	A
16	6	05/03/2011 16:30	07/03/2011 12:30	B
17
Sheet1

Excel 2007

 

[T. Valko]

Thanks Biff for the reply as well, appreciate all the time you guys put into these things - I'm so impressed - but totally confused!!

I don't really understand the networkdays function (other than it will show number of working days between two dates) so how come you guys use LOOKUP and VLOOKUP? I thought lookups, well, looked up something??

The first thing we need to do is calculate how many business hours there are from the start date/time to the end date/time.

Once we have that number then we have to "look it up" and compare that number to the defined intervals.

That's what V/LOOKUP does.

We've included the lookup "table" directly within the formula since there aren't that many variables, only 4.

Here's how it wold look if you created a lookup table on the worksheet:

<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=74 border=0 x:str><COLGROUP><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1184" span=2 width=37><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 28pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=37 height=17>0 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 28pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=37>A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>4 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>8 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>45 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">D</TD></TR></TBODY></TABLE>

Let's assume that table is in the range E1:F4.

36 is the number of calculated business hours.

=LOOKUP(36,E1:F4)

=VLOOKUP(36,E1:F4,2)

The result will be C.

36 is >= 8 and < 45.

 

[MikeWx]

Pat,

In both Biff's solution and mine, NETWORKDAYS told us how many full days of hours there were between DATE 1 and DATE 2. Then we calculated the number of partial day hours for DATE 1 and partial day hours for DATE 2. The LOOKUP or VLOOKUP determined the Severity Level category by looking up the total number of hours in the arrays at the end of our formulas and returning the corresponding Severity Level.

 

[scottishmovies]

WOW! You guys make it sound so easy!

I'm sure that many people searching for this solution will appareciate, as much as I do, the help you give to us all.

Thanks once again for explaining it to to us.

Kind regards,

Pat