Calculate hours and minutes between two dates and time showing Early, Late, On Time

[Zilla]

I am having problems with trying to find a formula for the results below.
Three columns below A = Expected, B = Actual, C = Diff.
Line one formula in cell C2 =A2-B2 result = 08:44. Which shows the ship time is early.
The problem is line 4,5,6 the time is late but all I see is #########.

What I would like to see is when the Actual Ship Time is earlier then Expected Ship Time and greater then 4 hours (240 min) show column D as "Early"
When Actual ship time is between 0 and 239 min show as On Time
When Actual ship time is later then 90 minutes past Expected Ship Time show as "Late" (IE:line 4, 5, 6 below)

Any help would be appreciated

Expected Ship Time	Actual Ship Time	Diff
2022-06-09 18:30	2022-06-09 9:46	08:44
2022-06-09 9:00	2022-06-09 5:36	03:24
2022-06-09 11:00	2022-06-09 10:22	00:38
2022-06-09 8:00	2022-06-09 8:11	#############	????
2022-06-09 17:00	2022-06-09 17:21	#############	????
2022-06-09 10:00	2022-06-09 10:27	#############	????

Book 1 Time sample.xlsx
	A	B	C
1	Expected Ship Time	Actual Ship Time	Diff
2	2022-06-09 18:30	2022-06-09 9:46	08:44
3	2022-06-09 9:00	2022-06-09 5:36	03:24
4	2022-06-09 11:00	2022-06-09 10:22	00:38
5	2022-06-09 8:00	2022-06-09 8:11	#############
6	2022-06-09 17:00	2022-06-09 17:21	#############
7	2022-06-09 10:00	2022-06-09 10:27	#############
sample

Cell Formulas
Range	Formula
C2:C7	C2	=A2-B2

 

[GraH]

Negative times cannot exist, that's why you have ##### result. The calculation in decimals however is correct.
For the early, on time and late calculation you can use expected shipdates + time (4,0,0) and + time(1,30,0) and compare the actual ship date. Do this Inside an IFS.

 

[Zilla]

How would I correct the #### to actually appear, is that possible?

Would it be possible to provide the IFS?

 

[GraH]

Based on your sample and my understanding of the given rules

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Expected Ship Time	Actual Ship Time	Diff	E/OT/L	delta in hours (abs value)	"late"			Early	4:00 AM
2	9/06/2022 18:30	9/06/2022 9:46	0,36389	Early	8:44:00	TRUE			Late	1:30 AM
3	9/06/2022 9:00	9/06/2022 5:36	0,14167	On time	3:24:00	TRUE			On time
4	9/06/2022 11:00	9/06/2022 10:22	0,02639	On time	0:38:00	TRUE
5	9/06/2022 8:00	9/06/2022 8:11	-0,00764	On time	0:11:00	FALSE	difference is smaler then 90 min
6	9/06/2022 17:00	9/06/2022 17:21	-0,01458	On time	0:21:00	FALSE	difference is smaler then 90 min
7	9/06/2022 10:00	9/06/2022 10:27	-0,01875	On time	0:27:00	FALSE	difference is smaler then 90 min
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IFS((B2:B7+J1)<=A2:A7,I1,(A2:A7+J2)<=A2:A7,I2,TRUE,I3)
F2:F7	F2	=B2:B7<=A2:A7
J1	J1	=TIME(4,0,0)
J2	J2	=TIME(0,90,0)
C2:C7	C2	=A2-B2
E2:E7	E2	=ABS(C2)
Dynamic array formulas.

 

[Zilla]

Hi GraH

This is great, the only issue I am having is column D I receive the following as a result #NAME?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Expected Ship Time	Actual Ship Time	Diff	E/OT/L	delta in hours (abs value)	"late"			Early	4:00 AM
2	6/10/2022 19:00	6/9/2022 9:52	1.38056	#NAME?	9:08	TRUE			Late	1:30 AM
3	6/10/2022 9:00	6/10/2022 4:03	0.20625	#NAME?	4:57	TRUE			On Time
4	6/10/2022 18:30	6/10/2022 8:06	0.43333	#NAME?	10:24	TRUE
5	6/10/2022 9:00	6/10/2022 4:50	0.17361	#NAME?	4:10	TRUE
6	6/10/2022 17:00	6/8/2022 12:38	2.18194	#NAME?	4:22	TRUE
7	6/10/2022 8:00	6/10/2022 8:10	-0.0069	#NAME?	0:10	FALSE
8	6/10/2022 18:00	6/10/2022 14:19	0.15347	#NAME?	3:41	TRUE
9	6/10/2022 12:00	6/10/2022 13:39	-0.0687	#NAME?	1:39	FALSE
10	6/10/2022 21:00	6/10/2022 17:15	0.15625	#NAME?	3:45	TRUE
11	6/10/2022 11:00	6/10/2022 12:31	-0.0632	#NAME?	1:31	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=A2-B2
D2:D11	D2	=IFS((B2:B7+J1)<=A2:A7,I1,(A2:A7+J2)<=A2:A7,I2,TRUE,I3)
E2:E11	E2	=ABS(C2)
F2:F11	F2	=B2:B7<=A2:A7
J1	J1	=TIME(4,0,0)
J2	J2	=TIME(0,90,0)

 

[GraH]

Could it be you do not have IFS function in your version? What's your excel version?

=IF((B4+J3)<=A4,I3,if((A4+J4)<=A4,I4,I5))

 

[Zilla]

Excel 2016

I tried the second formula and all is good.

I really appreciate you taking the time to provide the formula you saved me so much time.

Thank You

 

[GraH]

Thank you for the feedback it's working.
To help and learn from each other, that's the purpose of the forum, right?
Do consider to mark my post as solution. Most appreciated.

 

[Fluff]

Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)