Turn Around Times

[Andrew Skibbz]

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

 

[etaf]

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
	A	B	C	D	E	F
1	Start	Stop	days	Holidays		Fail/Met
2	2/5/21 10:00	2/10/21 10:00	1.000	1/4/21	Monday	MET
3	Friday	Wednesday		1/11/21	Monday
4				1/18/21	Monday
5				1/25/21	Monday
6				2/1/21	Monday
7				2/8/21	Monday
8				2/15/21	Monday
9				2/22/21	Monday
10				2/9/21	Tuesday
Sheet1

Cell Formulas
Range	Formula
C2	C2	=NETWORKDAYS.INTL(A2,B2,1,D2:D28)-1-MOD(A2,1)+MOD(B2,1)
F2	F2	=IF(C2>1,"FAIL","MET")
A3:B3	A3	=A2
D3:D9	D3	=D2+7
E2:E10	E2	=D2

 

[Andrew Skibbz]

Etaf, thats exactly what I wanted, many thanks

 

[etaf]

you are welcome

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