Formula for Excel business day turn around

R

RonB1111

Well-known Member

Joined

Nov 28, 2011

Messages

2,277

• May 28, 2012

• #2

Please clarify with some examples what the calculated result should be. Also why would it matter if the last email was 5/24 5:39pm that it was now May 25 - how does that impact the calculation? Also, is "proper business day turnaround 10 hours from the email date or some other length of time?

 

Upvote 0

C

cbjornn

New Member

Joined

May 28, 2012

Messages

9

• May 28, 2012

• #3

RonB1111 said:

Please clarify with some examples what the calculated result should be. Also why would it matter if the last email was 5/24 5:39pm that it was now May 25 - how does that impact the calculation? Also, is "proper business day turnaround 10 hours from the email date or some other length of time?

Click to expand...

Sure thing Ron!
I'm really quite a novice at this so hopefully my explanation makes sense.

So we keep a daily log of the email last worked on at the end of the day and the origin when that email first entered our system. Because we are not working 24 hours a day, or weekends, I want an accurate formula which shows the true amount of business days for us to respond.

Our business days are 10 hours, yes. So additionally, if someone sent us an email after business hours, it should count as that it came into our system at 7:00am the following day.

Here is an example:
A1 (Origin of email) - 5/24/2012 5:39:00 PM
A2 (When we responded to the email) - 5/25/2012 6:00:00 PM
A3 (Amount of business days we took to respond to the email) - ?

Please let me know if you have any further questions!

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,845

Office Version

1. 2010

Platform

1. Windows

• May 28, 2012

• #4

Duplicated at http://www.mrexcel.com/forum/showthread.php?t=638176

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• May 28, 2012

• #5

Hello cbjornn, I closed your original thread because you've opened a new one here. Please stick to one thread.

So what's the required answer for your example, are you looking for a result in hours and minutes?

You say the reply is at 6 PM, that's outside business hours, so not only might you receive the email outside business hours but you might also respond outside, is that right?

 

Upvote 0

C

cbjornn

New Member

Joined

May 28, 2012

Messages

9

• May 28, 2012

• #6

barry houdini said:

Hello cbjornn, I closed your original thread because you've opened a new one here. Please stick to one thread.

So what's the required answer for your example, are you looking for a result in hours and minutes?

You say the reply is at 6 PM, that's outside business hours, so not only might you receive the email outside business hours but you might also respond outside, is that right?

Click to expand...

No problem Barry.

My apologies, I meant 5:30pm, not 6pm. We always put 5:30pm for the email response time as that is when we close.

The preferred format would be # of business days with decimal, I.E. 2.21 business days. I'm not even sure if that is possible. If not, then hours and minutes are fine as well.

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• May 28, 2012

• #7

OK, if email receipt time is in A2....and that can be any time, evenings, weekends, holidays etc..... but B2 is the time you sent the response, always within work hours, then try this formula in C2

=NETWORKDAYS(A2,B2,H$2:H$10)-1+(MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1),"07:30","17:30"))/"10:00"

Assumes that you have a list of holiday dates in H2:H10

format C2 as number to show the decimal number of business days elapsed

 

Upvote 0

C

cbjornn

New Member

Joined

May 28, 2012

Messages

9

• May 28, 2012

• #8

Perfect, thank you!

This looks great. Just for my understanding, can you explain the last portion of this formula - /"10:00"?

Thanks again!

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• May 29, 2012

• #9

The first part calculates the whole days passed and then the last part calculates any hours above that so you might get 3 days and 6:00......but you want a decimal number of days so the 6:00 needs to be converted to a fraction of a day, to do that you can divide by the length of the day, that's "10:00", so when you have "6:00"/"10:00" that will give 0.6 and a result for my example of 3.6

 

Upvote 0