# Calculating between days/times

#### scottishmovies

##### Board Regular
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!

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### T. Valko

##### Well-known Member
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!

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

#### scottishmovies

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

regards,
Pat

#### T. Valko

##### Well-known Member
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

##### Board Regular

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

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

Pat

#### MikeWx

##### Active Member

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

#### T. Valko

##### Well-known Member
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"})

=(End_Time - Start_Time)

#### scottishmovies

##### Board Regular
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

##### Board Regular
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??

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,650
Messages
5,854,948
Members
431,689
Latest member
jacker01

### 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.

### Which adblocker are you using?

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

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