Time formula assistance

[Hakunabrotata]

Hi,

Could someone help me to build a set of 3 formula for the following query please?

I need to establish whether someone has successfully met their appointment time slot. They are allowed to arrive up to 45 minutes beforehand and be 15 minutes late. If they arrived within this hour slot, they will have made their appointment. If they are over 15 minutes late to the appointment time or arrive earlier than 45 minutes - they haven't met their appointment criteria.

Would someone be able to provide me with a formula for the following 3 cells:

- Arrived on time (within 45 mins of appt but no more than 15mins late)
- >45 early (arriving more than 45 minutes early)
- >15 minutes late (arriving over 15 mins late to appt time)

Any assistance would be greatly appreciated.

Thank you in advance.

 

[Hakunabrotata]

Or is it even possible to have the formula just in one cell - so it would present 3 possible answers?

For example:

Early (more than 45 mins early)
On time (Less than 45 mins early and no later than 15 mins)
Late (over 15 mins late)

 

[jdellasala]

This should be pretty much self explanatory (I HOPE!).

Book1
	A	B	C	D	E	F
1	Date/Time Format: mm/dd/yyyy hh:mm
2	Appointment	Arrived	On Time?	> 45 Minutes Early?	> 15 Late?
3	01/02/2023 09:25	01/02/2023 09:15	Yes	NO	NO
4	0.392361111	0.385416667	TRUE	NO	NO
5	01/02/2023 09:25	01/02/2023 09:26	NO	NO	NO
6	0.392361111	0.393055556	FALSE	NO	NO
7	01/02/2023 09:25	01/02/2023 08:26	Yes	Yes	NO
8	0.392361111	0.351388889	TRUE	Yes	NO
9	01/02/2023 09:25	01/02/2023 10:26	NO	NO	Yes
10	0.392361111	0.434722222	FALSE	NO	Yes
11
12	Seconds in a Day:	86,400
13	Seconds:	60
14	One Second in Excel:	0.000011574074074
15	60 Seconds:	0.000694444444444
16
17		Format: 15 Decimals	Format: [h]:mm:ss;@		Format: 15 Decimals	Format: h:mm;@
18	60 Seconds:	0.000000000000000	0:00:00	60 Minutes:	0.000000000000000	0:00
19		0.000011574074074	0:00:01		0.000694444444444	0:01
20		0.000023148148148	0:00:02		0.001388888888889	0:02
Sheet1

Cell Formulas
Range	Formula
C3,C5,C7,C9	C3	=IF(MOD(A3,1)>=MOD(B3,1),"Yes","NO")
D3:D10	D3	=IF(A3-B3>0.03125, "Yes", "NO")
E3:E10	E3	=IF(A3-B3<-0.010417, "Yes", "NO")
C4,C6,C8,C10	C4	=A4>B4
A4:B4,A10:B10,A8:B8,A6:B6	A4	=MOD(A3,1)
B12	B12	=60*60*24
B14	B14	=1/(60*60*24)
B15	B15	=B14*60
B18:B77	B18	=SEQUENCE(B13,,0,B14)
C18:C77	C18	=SEQUENCE(B13,,0,B14)
E18:E77	E18	=SEQUENCE(60,,0,B15)
F18:F77	F18	=SEQUENCE(60,,0,B15)
Dynamic array formulas.

Since you have 365, if you paste that into Cell A1 (I always forget to check!), you'll see that the formulas used in Row 18 result in arrays of 60 seconds and 60 minutes. I included the formatting notes because it doesn't come through.

 

[jdellasala]

For a single cell, you could put this in cell G3 above and get this:

Book1
	G	H	I
3	Yes	NO	NO
Sheet1

Cell Formulas
Range	Formula
G3:I3	G3	=HSTACK(IF(MOD(A3,1)>=MOD(B3,1),"Yes","NO"),IF(A3-B3>0.03125, "Yes", "NO"),IF(A3-B3<-0.010417, "Yes", "NO"))
Dynamic array formulas.

but what's the point?
You could do it in Power Query too, but we won't go there!

 

[Sufiyan97]

Another option

Book5
	A	B	C	D
1	Appoinement	Arrived	Comment
2	1/2/2023 9:10	1/2/2023 8:26	On Time
3	1/2/2023 9:10	1/2/2023 9:26	Late
4	1/2/2023 9:10	1/2/2023 8:24	Early
5
6
7
Sheet3

Cell Formulas
Range	Formula
C2:C4	C2	=IF(AND(B2<A2,MAX(A2:B2)-MIN(A2:B2)-INT(MAX(A2:B2)-MIN(A2:B2))>=TIME(0,45,0)),"Early",IF(AND(B2<A2,MAX(A2:B2)-MIN(A2:B2)-INT(MAX(A2:B2)-MIN(A2:B2))<=TIME(0,45,0)),"On Time","Late"))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E
1
2	02/01/2023 09:10	02/01/2023 09:15	Y	N	N
3	02/01/2023 09:10	02/01/2023 09:56	N	Y	N
4	02/01/2023 09:31	02/01/2023 09:15	N	N	Y
Main

Cell Formulas
Range	Formula
C2:C4	C2	=IF(MEDIAN(A2,B2-TIME(0,45,0),B2+TIME(0,15,0))=A2,"Y","N")
D2:D4	D2	=IF(A2<B2-TIME(0,45,0),"Y","N")
E2:E4	E2	=IF(A2>B2+TIME(0,15,0),"Y","N")

 

[Hakunabrotata]

Thank you all for the guidance and recommendations - it's greatly appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.