Turn Around Times

Andrew Skibbz

New Member
Joined
Mar 29, 2017
Messages
27
Hi All,

I need to know the time (hh:mm:ss) it has taken between two dates 'arrived date' and 'received date'.

- The arrived date is when an 'order' has been placed by the customer.
- The received date is when it was 'booked onto the system by us.

We have 24hrs to book an order into the system, so I want to see how long its is taking us to effectively process the order (arrived vs received)

The formula would need to include the following caveats:

1) If the arrived date and time falls on a Friday at 10am (e.g. 12/02/21 10:00:00) - (excluding the Weekend and Mondays) we would have until 10am the following Tuesday (16/02/21 10:00:00) to enter a 'received date'. This would count as 24hrs. not 4 days.

2) If the arrived date and time falls on a Friday which includes a Bank Holiday, then we would have until 10am the following Wednesday as the Tuesday would count as the Monday in this case.

What I eventually want to end up with is a bar chart which shows the time is has taken us (hh:mm:ss) between the two dates (e.g 0-6 hrs, 6-12 hrs, 12-18hrs, 18-24hrs).

I would very much appreciate your help on this please.

Regards,

Andrew
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,591
Office Version
  1. 365
Platform
  1. MacOS
networkdays.intl() with a range of the holidays, which would be Mondays and any bankhoidays - you would need to add the Tuesday
then use
=NETWORKDAYS.INTL(A2,B2,1,D2:D28)-1-MOD(A2,1)+MOD(B2,1)
if within 24 hours - would result in a 1 or less

Book8
ABCDEF
1StartStopdaysHolidaysFail/Met
22/5/21 10:002/10/21 10:001.0001/4/21MondayMET
3FridayWednesday1/11/21Monday
41/18/21Monday
51/25/21Monday
62/1/21Monday
72/8/21Monday
82/15/21Monday
92/22/21Monday
102/9/21Tuesday
Sheet1
Cell Formulas
RangeFormula
C2C2=NETWORKDAYS.INTL(A2,B2,1,D2:D28)-1-MOD(A2,1)+MOD(B2,1)
F2F2=IF(C2>1,"FAIL","MET")
A3:B3A3=A2
D3:D9D3=D2+7
E2:E10E2=D2
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,591
Office Version
  1. 365
Platform
  1. MacOS
you are welcome

on reflection you could probably use networkdays() rather than networkdays.intl()
as you are not using an offset weekend
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

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
Top