Calculating between days/times

scottishmovies

Board Regular
Joined
Mar 11, 2003
Messages
158
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
ABCDEF
1
2Work Days = Mon-FriWork Times = 08:30 - 17:30
3
4Severity Level
5A<4 hours
6B> 4 hours<8 hours
7C> 8 hours< 1 week
8D> 1 week
9
10Date 1Date 2Expected response
11101/03/2011 12:0001/03/2011 15:15A
12201/03/2011 12:0001/03/2011 16:15B
13301/03/2011 12:0002/03/2011 14:00C
14401/03/2011 12:0007/03/2011 12:00D
15505/03/2011 16:0007/03/2011 09:00A
16605/03/2011 16:3007/03/2011 12:30B
17
Sheet1
Excel 2007
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
ABCDEF
1
2Work Days = Mon-FriWork Times = 08:30 - 17:30
3
4Severity Level
5A<4 hours
6B> 4 hours<8 hours
7C> 8 hours< 1 week
8D> 1 week
9
10Date 1Date 2Expected response
11101/03/2011 12:0001/03/2011 15:15A
12201/03/2011 12:0001/03/2011 16:15B
13301/03/2011 12:0002/03/2011 12:00C
14401/03/2011 12:0007/03/2011 12:00D
15505/03/2011 16:0007/03/2011 09:00A
16605/03/2011 16:3007/03/2011 12:30B
17
Sheet1
Excel 2007
Will the Date1, Date2 times ALWAYS be within work times (business hours)?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.

HTML:
=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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top