Turn Around Times

Andrew Skibbz

New Member
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).

Regards,

Andrew

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

Andrew Skibbz

New Member
Etaf, thats exactly what I wanted, many thanks

etaf

Well-known Member
you are welcome

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

Replies
3
Views
146
Replies
7
Views
388
Replies
1
Views
123
Replies
10
Views
440
Replies
0
Views
81

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.

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