Formula help between booking time and actual arrival time.

[HissingDust]

Hello,

I am trying to create a formula that will tell me if a driver arrived at my site, early late or on time.

I have some KPIs to use:

Within 15 mins

Inside Booking Window Hour

Missed Slot 16m to 1Hr Early

Missed Slot 1Hr to 3Hrs Early

Missed Slot Over 3Hrs Early

Missed Slot 1Hr to 3Hrs Late

Missed Slot Over 3Hrs Late

Unfulfilled = Blank cell in registration column D

My booked times start in C2 and actual arrival time is D2. I have also been struggling because the time sometimes cross into a different day.

Booking time	Registration
2022-01-14 00:30	2022-01-13 23:00

Any help is much appreciated.

Thanks

 

[HissingDust]

I have been using this formula I found on another forum. I would just like to know how to tailor it to my KPIs.

=IF(ISBLANK(D1),"",IF(D2 < C2,"Early",IF(D2=C2,"On time",IF(D2-C2<=30/1440,"Needs improving","LATE"))))

 

[Toadstool]

Hi HissingDust,

I'me not sure about your "Inside Booking Window Hours" as that would seem to also be within 15 minites and 16 minutes to 1 hour, negative and positive, but this should work for you other checks.

HissingDust.xlsx
	C	D	E
1	Booked	Arrived	Message
2	01-Jan-22 06:00:00	01-Jan-21 00:00:00	Missed Slot Over 3hrs Early
3	01-Jan-22 06:00:00	01-Jan-22 02:00:00	Missed Slot Over 3hrs Early
4	01-Jan-22 06:00:00	01-Jan-22 04:15:00	Missed Slot 1Hr to 3Hrs Early
5	01-Jan-22 06:00:00	01-Jan-22 05:30:00	Missed Slot 16m to 1Hr Early
6	01-Jan-22 06:00:00	01-Jan-22 05:46:00	Within 15 mins
7	01-Jan-22 06:00:00	01-Jan-22 06:01:00	Within 15 mins
8	01-Jan-22 06:00:00	01-Jan-22 06:14:00	Within 15 mins
9	01-Jan-22 06:00:00	01-Jan-22 06:16:00	Missed Slot 16m to 1hr Late
10	01-Jan-22 06:00:00	01-Jan-22 06:31:00	Missed Slot 16m to 1hr Late
11	01-Jan-22 06:00:00	01-Jan-22 06:46:00	Missed Slot 16m to 1hr Late
12	01-Jan-22 06:00:00	01-Jan-22 07:01:00	Missed Slot 1hr to 3hrs Late
13	01-Jan-22 06:00:00	01-Jan-22 07:16:00	Missed Slot 1hr to 3hrs Late
14	01-Jan-22 06:00:00	01-Jan-22 07:31:00	Missed Slot 1hr to 3hrs Late
15	01-Jan-22 06:00:00	01-Jan-22 07:46:00	Missed Slot 1hr to 3hrs Late
16	01-Jan-22 06:00:00	01-Jan-22 11:01:00	Missed Slot Over 3hrs Late
17	01-Jan-22 06:00:00	01-Jan-22 11:16:00	Missed Slot Over 3hrs Late
18	01-Jan-22 06:00:00	30-Nov-22 11:16:00	Missed Slot Over 3hrs Late
19	01-Jan-22 06:00:00		Unfulfilled
20
Sheet1 (2)

Cell Formulas
Range	Formula
E2:E20	E2	=IF(C2="","",IF(D2="","Unfulfilled",INDEX({"Missed Slot Over 3hrs Early","Missed Slot 1Hr to 3Hrs Early","Missed Slot 16m to 1Hr Early","Within 15 mins","Missed Slot 16m to 1hr Late","Missed Slot 1hr to 3hrs Late","Missed Slot Over 3hrs Late"},MATCH((VALUE(D2-C2)),{-99999,-0.125,-0.04167,-0.01042,0.01042,0.041667,0.125,99999},1))))

 

[Peter_SSs]

I'me not sure about your "Inside Booking Window Hours" as that would seem to also be within 15 minites and 16 minutes to 1 hour, negative and positive,

There is no 16 minutes to 1 hour late in that original list so just guessing that "Inside Booking Window Hour" might mean that
.. or possibly it might mean 0 to 1 hour late and within 15 minutes might only apply to early arrival.

I know the OP needs to specify, but tossing in a couple of ideas for consideration/clarification.

 

[HissingDust]

Hi HissingDust,

I'me not sure about your "Inside Booking Window Hours" as that would seem to also be within 15 minites and 16 minutes to 1 hour, negative and positive, but this should work for you other checks.

HissingDust.xlsx
	C	D	E
1	Booked	Arrived	Message
2	01-Jan-22 06:00:00	01-Jan-21 00:00:00	Missed Slot Over 3hrs Early
3	01-Jan-22 06:00:00	01-Jan-22 02:00:00	Missed Slot Over 3hrs Early
4	01-Jan-22 06:00:00	01-Jan-22 04:15:00	Missed Slot 1Hr to 3Hrs Early
5	01-Jan-22 06:00:00	01-Jan-22 05:30:00	Missed Slot 16m to 1Hr Early
6	01-Jan-22 06:00:00	01-Jan-22 05:46:00	Within 15 mins
7	01-Jan-22 06:00:00	01-Jan-22 06:01:00	Within 15 mins
8	01-Jan-22 06:00:00	01-Jan-22 06:14:00	Within 15 mins
9	01-Jan-22 06:00:00	01-Jan-22 06:16:00	Missed Slot 16m to 1hr Late
10	01-Jan-22 06:00:00	01-Jan-22 06:31:00	Missed Slot 16m to 1hr Late
11	01-Jan-22 06:00:00	01-Jan-22 06:46:00	Missed Slot 16m to 1hr Late
12	01-Jan-22 06:00:00	01-Jan-22 07:01:00	Missed Slot 1hr to 3hrs Late
13	01-Jan-22 06:00:00	01-Jan-22 07:16:00	Missed Slot 1hr to 3hrs Late
14	01-Jan-22 06:00:00	01-Jan-22 07:31:00	Missed Slot 1hr to 3hrs Late
15	01-Jan-22 06:00:00	01-Jan-22 07:46:00	Missed Slot 1hr to 3hrs Late
16	01-Jan-22 06:00:00	01-Jan-22 11:01:00	Missed Slot Over 3hrs Late
17	01-Jan-22 06:00:00	01-Jan-22 11:16:00	Missed Slot Over 3hrs Late
18	01-Jan-22 06:00:00	30-Nov-22 11:16:00	Missed Slot Over 3hrs Late
19	01-Jan-22 06:00:00		Unfulfilled
20
Sheet1 (2)

Cell Formulas
Range	Formula
E2:E20	E2	=IF(C2="","",IF(D2="","Unfulfilled",INDEX({"Missed Slot Over 3hrs Early","Missed Slot 1Hr to 3Hrs Early","Missed Slot 16m to 1Hr Early","Within 15 mins","Missed Slot 16m to 1hr Late","Missed Slot 1hr to 3hrs Late","Missed Slot Over 3hrs Late"},MATCH((VALUE(D2-C2)),{-99999,-0.125,-0.04167,-0.01042,0.01042,0.041667,0.125,99999},1))))

Thank you so much for your help. I don't think I would have cracked that within the next 10 years.

 

[Peter_SSs]

Thank you so much for your help. I don't think I would have cracked that within the next 10 years.

Are you saying that "Inside Booking Window Hour" can never be the result?

 

[Toadstool]

Thank you so much for your help. I don't think I would have cracked that within the next 10 years.

You're welcome!

Let me explain in case you have similar requirements again.

The key is MATCH which usually has a match_type of zero or FALSE, meaning find me an exact match. In this case I'm using a match_type of 1 which the Microsoft documentation explains "MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order."

That last high value of all the 9's is unnecessary and I embedded the INDEX and MATCH in the formula but this is probably easier to explain if I put the range and message in a table. Columns G and H are just there to show the interim values.

HissingDust.xlsx
	C	D	E	F	G	H	I	J	K
1	Booked	Arrived	Message		MATCH	D2 minus C2		Arrived minus Booked	Message
2	01-Jan-22 06:00:00	01-Jan-21 00:00:00	Missed Slot Over 3hrs Early		1	-365.25000		-9999.00000	Missed Slot Over 3hrs Early
3	01-Jan-22 06:00:00	01-Jan-22 02:00:00	Missed Slot Over 3hrs Early		1	-0.16667		-0.12500	Missed Slot 1Hr to 3Hrs Early
4	01-Jan-22 06:00:00	01-Jan-22 04:15:00	Missed Slot 1Hr to 3Hrs Early		2	-0.07292		-0.04167	Missed Slot 16m to 1Hr Early
5	01-Jan-22 06:00:00	01-Jan-22 05:30:00	Missed Slot 16m to 1Hr Early		3	-0.02083		-0.01042	Within 15 mins
6	01-Jan-22 06:00:00	01-Jan-22 05:46:00	Within 15 mins		4	-0.00972		0.01042	Missed Slot 16m to 1hr Late
7	01-Jan-22 06:00:00	01-Jan-22 06:01:00	Within 15 mins		4	0.00069		0.04167	Missed Slot 1hr to 3hrs Late
8	01-Jan-22 06:00:00	01-Jan-22 06:14:00	Within 15 mins		4	0.00972		0.12500	Missed Slot Over 3hrs Late
9	01-Jan-22 06:00:00	01-Jan-22 06:16:00	Missed Slot 16m to 1hr Late		5	0.01111
10	01-Jan-22 06:00:00	01-Jan-22 06:31:00	Missed Slot 16m to 1hr Late		5	0.02153
11	01-Jan-22 06:00:00	01-Jan-22 06:46:00	Missed Slot 16m to 1hr Late		5	0.03194
12	01-Jan-22 06:00:00	01-Jan-22 07:01:00	Missed Slot 1hr to 3hrs Late		6	0.04236
13	01-Jan-22 06:00:00	01-Jan-22 07:16:00	Missed Slot 1hr to 3hrs Late		6	0.05278
14	01-Jan-22 06:00:00	01-Jan-22 07:31:00	Missed Slot 1hr to 3hrs Late		6	0.06319
15	01-Jan-22 06:00:00	01-Jan-22 07:46:00	Missed Slot 1hr to 3hrs Late		6	0.07361
16	01-Jan-22 06:00:00	01-Jan-22 11:01:00	Missed Slot Over 3hrs Late		7	0.20903
17	01-Jan-22 06:00:00	01-Jan-22 11:16:00	Missed Slot Over 3hrs Late		7	0.21944
18	01-Jan-22 06:00:00	30-Nov-22 11:16:00	Missed Slot Over 3hrs Late		7	333.21944
19	01-Jan-22 06:00:00		Unfulfilled
2nd

Cell Formulas
Range	Formula
G2:G18	G2	=MATCH((VALUE(D2-C2)),$J$2:$J$8,1)
H2:H18	H2	=D2-C2
E2:E19	E2	=IF(C2="","",IF(D2="","Unfulfilled",INDEX($K$2:$K$8,MATCH((VALUE(D2-C2)),$J$2:$J$8,1))))

That -9999 is 9999 days ago, which is about 28 years ago. You can verify this by changing an Arrived date to 28 years ago and the MATCH will fail with a #N/A error as it will be less than -9999 days ago.