Time variance formula

[Burwell]

Good Afternoon.

I have a list of Planned arrival times and a list of actual arrive times and looking for an excel formula to highlight if their is a 15 min variance both earlier or later than the original planned time?

Hoping someone can help

Thank you

 

[6StringJazzer]

When asking an Excel question, always give the details about what your data looks like, and what cells it is in. Is your data in rows? Columns? Which ones?

For this response I will assume that your planned times are in column A starting in row 1, and your actual times are in column B starting in row 1.

I have assumed that by "15 min variance both earlier or later" you mean "at least a 15 min variance both earlier or later".

You used the word "highlight" so you want to use conditional formatting. Select columns A and B and then create a conditional formatting rule using a formula:

=ABS($A1-$B1)>=TIME(0,14,0)

 

[Toadstool]

Hi Burwell,

Time without a date is always tricky as spanning midnight takes you to the next day. Here I'm assuming that if your actual is less than the planned by more than 12 hours then it's the next day.

Burwell.xlsx
	E	F	G
1	Planned Arrival	Actual Arrival	Variance hh:mm
2	12:30	12:42	00:12
3	13:45	14:04	00:19
4	15:00	12:00	03:00
5	16:15	16:39	00:24
6	17:30	17:30	00:00
7	18:45	18:55	00:10
8	20:00	19:55	00:05
9	21:15	20:45	00:30
10	22:30	22:44	00:14
11	23:45	1:30	01:45
12	1:00	2:22	01:22
13	2:15	3:08	00:53
14	3:30	15:30	12:00
15	4:45	16:00	11:15
16	6:00	6:11	00:11
1st

Cell Formulas
Range	Formula
G2:G16	G2	=TEXT(ABS(IF(F2+TIME(12,0,0)<E2,1+F2,F2)-E2),"hh:mm")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F18	Expression	=ABS(IF(F2+TIME(12,0,0)<E2,1+F2,F2)-E2)>TIME(0,15,0)	text	NO