Conditional Formatting if difference in time is equal to certain value

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,877
Office Version
  1. 365
Platform
  1. MacOS
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
BC
212:30:0012:32:00
312:30:0013:00:00
412:30:0012:31:00
512:30:0012:32:00
612:30:0012:32:00
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:C6Expression=OR(MROUND($C2 - $B2, 1/86400) = TIME(0,1,0),MROUND($C2 - $B2, 1/86400) = TIME(0,30,0))textNO


With dates added to the cells
Book1-etaf.xlsx
BC
21/1/21 12:301/1/21 12:32
31/1/21 12:301/1/21 12:31
41/1/21 12:301/1/21 12:32
51/4/21 12:301/10/21 12:31
61/1/21 12:301/1/21 12:32
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:C6Expression=OR(MROUND($C2 - $B2, 1/86400) = TIME(0,1,0),MROUND($C2 - $B2, 1/86400) = TIME(0,30,0))textNO
 

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEF
3DateStart Time (hh:mm)Lunch Start (hh:mm)Lunch End (hh:mm)End Time (hh:mm)Hours (decimal)
43/15/20216:459:142.483
53/15/20219:1510:140.983
63/15/202110:1510:490.567
73/15/202110:5014:0014:3016:094.817
83/15/202116:1016:490.650
93/15/202116:5017:290.650
103/15/202117:3018:180.800
113/19/20216:457:440.983
123/19/20217:4510:292.733
133/19/202110:3012:041.567
143/19/202112:0513:000.917
153/19/202113:3013:590.483
Sheet1
Cell Formulas
RangeFormula
F4:F15F4=IF(E4="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:F15Expression=$B4="N"textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,877
Office Version
  1. 365
Platform
  1. MacOS
did you try the formula with the changes for your data ?
 

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
ABCDEFG
3DateStart Time (hh:mm)Lunch Start (hh:mm)Lunch End (hh:mm)End Time (hh:mm)Hours (decimal)Column1
43/15/20216:459:142.483TRUE
53/15/20219:1510:140.983TRUE
63/15/202110:1510:490.567TRUE
73/15/202110:5014:0014:3016:094.817TRUE
83/15/202116:1016:490.650TRUE
93/15/202116:5017:290.650TRUE
103/15/202117:3018:180.800 
113/19/20216:457:440.983TRUE
123/19/20217:4510:292.733TRUE
133/19/202110:3012:041.567TRUE
143/19/202112:0513:000.917TRUE
153/19/202113:3013:590.483 
Sheet1
Cell Formulas
RangeFormula
F4:F15F4=IF(E4="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)
G4:G15G4=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
CellConditionCell FormatStop If True
B4:B15Expression=IF($A5=$A4,OR(MROUND($B5-$E4,1/86400)=TIME(0,1,0),MROUND($B5-$E4,1/86400)=TIME(0,30,0)))textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,877
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFG
1DateStart Time (hh:mm)Lunch Start (hh:mm)Lunch End (hh:mm)End Time (hh:mm)Hours (decimal)Column1
23/15/202106:45:0009:14:00=IF(E4="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24) 
33/15/202109:15:0010:14:00=IF(E5="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
43/15/202110:15:0010:49:00=IF(E6="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
53/15/202110:50:000.5833333330.60416666716:09:00=IF(E7="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
63/15/202116:10:0016:49:00=IF(E8="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
73/15/202116:50:0017:29:00=IF(E9="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
83/15/202117:30:0018:18:00=IF(E10="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
93/19/202106:45:0007:44:00=IF(E11="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24) 
103/19/202107:45:0010:29:00=IF(E12="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
113/19/202110:30:0012:04:00=IF(E13="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
123/19/202112:05:0013:00:00=IF(E14="","",(([@[End Time (hh:mm)]]-[@[Start Time (hh:mm)]])-([@[Lunch End (hh:mm)]]-[@[Lunch Start (hh:mm)]]))*24)TRUE
133/19/202113:30:0013: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
RangeFormula
G2:G13G2=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
CellConditionCell FormatStop If True
B2:E13Expression=AND($A1=$A2,OR(MROUND($B2-$E1,1/86400)=TIME(0,1,0),MROUND($B2-$E1,1/86400)=TIME(0,30,0)))textNO
 
Solution

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Ok, that worked using the AND function.

Thank you for your help!
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,877
Office Version
  1. 365
Platform
  1. MacOS
you are welcome, glad its sorted for you now
 

Forum statistics

Threads
1,141,413
Messages
5,706,301
Members
421,441
Latest member
VapesRub

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top