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]

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 diving me nuts!

Thanks in adance,
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 12: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

Will the Date1, Date2 times ALWAYS be within work times (business hours)?

 

[scottishmovies]

Yes, the dates will ALWAYS be within work time/days.

regards,
Pat

 

[T. Valko]

Yes, the dates will ALWAYS be within work time/days.

regards,
Pat

Ok, we need to better define the levels...

A week... I guess that could be converted to hours of a work week? 5 days in the work week times 9 hrs per work day = 45hrs.

Your levels are leaving out some figures:

< 4 hours
> 4 hours < 8 hours
> 8 hours < 1 week
> 1 week

You're skipping over 4 hrs, 8hrs, and 1 "week".

Typically, you'd want something like this:

< 4 hours
>= 4 hours < 8 hours
>= 8 hours < 1 week
>= 1 week

 

[scottishmovies]

Hi Biff,

Yes, you're right. The levels you have are better defined. And a week would be 45 hours as it's 5x9 as long as I can still exclude the weekend.

Pat

 

[scottishmovies]

Does that make it easier or more complicated to do a formula for this?

Pat

 

[MikeWx]

Pat,

Your last two examples start on March 5, 2011; which is a Saturday. Are these valid?

Also, the fourth example starting at 12:00 on March 1st and finishing at 12:00 on March 7th was completed in 36 hours (March 1st, 5.5 hours, March 2nd, 9 hours; March 3rd, 9 hours; March 4th, 9 hours; March 7th, 3.5 hours); so it should have a Severity Level of C.

The following formula will give you the values you are looking for if both Date1 and Date2 are within the Work Days and Work Times that you have defined.

=VLOOKUP(NETWORKDAYS(B11,C11)*9-((B11-(INT(B11)+(8.5/24)))*24)-(((INT(C11)+(17.5/24)-C11))*24),{0,"A";4,"B";8,"C";45,"D"},2,1)

Mike

 

[T. Valko]

Hi Biff,

Yes, you're right. The levels you have are better defined. And a week would be 45 hours as it's 5x9 as long as I can still exclude the weekend.

Pat

Ok, there is still a problem with the sample data you posted...

A couple of the start dates are Saturdays. You said the dates/times will only be within business days/times.

This sample:

1/3/2011 12:00 --- 7/2/2011 12:00 --- D

I believe the correct result should be C. The total hours between the dates is 36 which falls into the >=8 and < 1 week (45hrs) level.

Anyhow here's the generic formula written in "pseudo code".

=LOOKUP(((NETWORKDAYS(Start_Date/Time,End_Date/Time)-1)*0.375+MOD(End_Date/Time,1)-MOD(Start_Date/Time,1))*24,{0;4;8;45},{"A";"B";"C";"D"})

0.375 = business hours per business day.
=(End_Time - Start_Time)

 

[scottishmovies]

Hi Mike,

FANTASTIC! Absolutely spot on for wht I need. You were right that I had gien some incorrect data (was just a mock up and made a mess up of it!)

But your solution is exactly what I was looking for.
Many thanks for your speedy response! Your help was very much appreciated.

All the best,
Pat

 

[scottishmovies]

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??