Conditional Formatting if difference in time is equal to certain value

[NWPhotoExplorer]

I'm struggling to come up with a formula that will look at the start time and subtract it from the end time (if the dates are the same) and if the difference is 1 minute or 30 minutes, color it green. I've tried googling this and have not come up with anything that works at all.

I'm hoping that someone here might have some suggestions or pointers to get me headed in the right direction.

Thanks!

 

[etaf]

Not sure what you mean exactly by the date - Is the DATE part of the TIME in the Cell
14/12/21 12:30
14/12/21 12:31
OR is the date in a different cell ?

This should work if same date in cell

I suspect this could be simplified BUT try
=OR(MROUND($C2 - $B2, 1/86400) = TIME(0,1,0),MROUND($C2 - $B2, 1/86400) = TIME(0,30,0))

Book1-etaf.xlsx
	B	C
2	12:30:00	12:32:00
3	12:30:00	13:00:00
4	12:30:00	12:31:00
5	12:30:00	12:32:00
6	12:30:00	12:32:00
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C6	Expression	=OR(MROUND($C2 - $B2, 1/86400) = TIME(0,1,0),MROUND($C2 - $B2, 1/86400) = TIME(0,30,0))	text	NO

With dates added to the cells

Book1-etaf.xlsx
	B	C
2	1/1/21 12:30	1/1/21 12:32
3	1/1/21 12:30	1/1/21 12:31
4	1/1/21 12:30	1/1/21 12:32
5	1/4/21 12:30	1/10/21 12:31
6	1/1/21 12:30	1/1/21 12:32
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C6	Expression	=OR(MROUND($C2 - $B2, 1/86400) = TIME(0,1,0),MROUND($C2 - $B2, 1/86400) = TIME(0,30,0))	text	NO

 

[NWPhotoExplorer]

The date is in its own field. So basically we have to log time for each job, but with the way the system was set up when a job ends at 9:14 am, the next job can't start until 9:15 am. So I built this simple sheet to keep track of time spent on a job, but sometimes people overlap their time and it errors out when entering into the timekeeping system. I was looking for a way to color-code the start time if it was 1 minute past the previous end time if it was on the same day.

As for the 30 minutes I mentioned, sometimes we take our lunch break after the job ends, so there may be a 30-minute gap between jobs as we haven't started a job but stopped to take a lunch break.

Job Tracker.xlsx
	A	B	C	D	E	F
3	Date	Start Time (hh:mm)	Lunch Start (hh:mm)	Lunch End (hh:mm)	End Time (hh:mm)	Hours (decimal)
4	3/15/2021	6:45			9:14	2.483
5	3/15/2021	9:15			10:14	0.983
6	3/15/2021	10:15			10:49	0.567
7	3/15/2021	10:50	14:00	14:30	16:09	4.817
8	3/15/2021	16:10			16:49	0.650
9	3/15/2021	16:50			17:29	0.650
10	3/15/2021	17:30			18:18	0.800
11	3/19/2021	6:45			7:44	0.983
12	3/19/2021	7:45			10:29	2.733
13	3/19/2021	10:30			12:04	1.567
14	3/19/2021	12:05			13:00	0.917
15	3/19/2021	13:30			13:59	0.483
Sheet1

Cell Formulas
Range	Formula
F4:F15	F4	=IF(E4="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:F15	Expression	=$B4="N"	text	NO

 

[etaf]

did you try the formula with the changes for your data ?

 

[NWPhotoExplorer]

Yes, I did.

So to test the formula, I added a column in G with the formula and it seems to work there showing TRUE when it's correct and FALSE when it's not 1 minute or 30 minutes different. But I cannot get the conditional formatting to work now. I'm not sure where I am messing this up. Right now, it's coloring the start time for the day GREEN, which it shouldn't, and it's not coloring the last job of the day green, even though it's 1 minute later than the previous end time. It must be something simple, but I can't see it.

Job Tracker.xlsx
	A	B	C	D	E	F	G
3	Date	Start Time (hh:mm)	Lunch Start (hh:mm)	Lunch End (hh:mm)	End Time (hh:mm)	Hours (decimal)	Column1
4	3/15/2021	6:45			9:14	2.483	TRUE
5	3/15/2021	9:15			10:14	0.983	TRUE
6	3/15/2021	10:15			10:49	0.567	TRUE
7	3/15/2021	10:50	14:00	14:30	16:09	4.817	TRUE
8	3/15/2021	16:10			16:49	0.650	TRUE
9	3/15/2021	16:50			17:29	0.650	TRUE
10	3/15/2021	17:30			18:18	0.800
11	3/19/2021	6:45			7:44	0.983	TRUE
12	3/19/2021	7:45			10:29	2.733	TRUE
13	3/19/2021	10:30			12:04	1.567	TRUE
14	3/19/2021	12:05			13:00	0.917	TRUE
15	3/19/2021	13:30			13:59	0.483
Sheet1

Cell Formulas
Range	Formula
F4:F15	F4	=IF(E4="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)
G4:G15	G4	=IF($A5=$A4,OR(MROUND($B5-$E4,1/86400)=TIME(0,1,0),MROUND($B5-$E4,1/86400)=TIME(0,30,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B15	Expression	=IF($A5=$A4,OR(MROUND($B5-$E4,1/86400)=TIME(0,1,0),MROUND($B5-$E4,1/86400)=TIME(0,30,0)))	text	NO

 

[etaf]

hows this
=AND($A1=$A2,OR(MROUND($B2-$E1,1/86400)=TIME(0,1,0),MROUND($B2-$E1,1/86400)=TIME(0,30,0)))

Book1
	A	B	C	D	E	F	G
1	Date	Start Time (hh:mm)	Lunch Start (hh:mm)	Lunch End (hh:mm)	End Time (hh:mm)	Hours (decimal)	Column1
2	3/15/2021	06:45:00			09:14:00	=IF(E4="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)
3	3/15/2021	09:15:00			10:14:00	=IF(E5="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
4	3/15/2021	10:15:00			10:49:00	=IF(E6="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
5	3/15/2021	10:50:00	0.583333333	0.604166667	16:09:00	=IF(E7="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
6	3/15/2021	16:10:00			16:49:00	=IF(E8="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
7	3/15/2021	16:50:00			17:29:00	=IF(E9="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
8	3/15/2021	17:30:00			18:18:00	=IF(E10="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
9	3/19/2021	06:45:00			07:44:00	=IF(E11="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)
10	3/19/2021	07:45:00			10:29:00	=IF(E12="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
11	3/19/2021	10:30:00			12:04:00	=IF(E13="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
12	3/19/2021	12:05:00			13:00:00	=IF(E14="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
13	3/19/2021	13:30:00			13:59:00	=IF(E15="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)	TRUE
Sheet3

Cell Formulas
Range	Formula
G2:G13	G2	=IF($A1=$A2,OR(MROUND($B2-$E1,1/86400)=TIME(0,1,0),MROUND($B2-$E1,1/86400)=TIME(0,30,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E13	Expression	=AND($A1=$A2,OR(MROUND($B2-$E1,1/86400)=TIME(0,1,0),MROUND($B2-$E1,1/86400)=TIME(0,30,0)))	text	NO

 

[NWPhotoExplorer]

Ok, that worked using the AND function.

Thank you for your help!

 

[etaf]

you are welcome, glad its sorted for you now