conditional formatting issue

G

G3N3RAL31

New Member
Joined
Aug 18, 2018
Messages
3
I am trying to utilize a break gap spreadsheet and add conditional formatting to it to highlight when an employee takes their break within a window of time and for the CF to color highlight anyone outside of the specific threshold of time.
example:
employee's 1st break is scheduled at 7:00
window to review is set at 6:45 to 7:45 (variable range)
employee 1st break is set for 20 minutes in length
CF to read time between range and color cell range anything greater than threshold (20 minutes)

Not able to update image of the spreadsheet.

[TABLE="width: 1510"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]Adjust time range in yellow by shift[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Site ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]1st Break[/TD]
[TD]6:55[/TD]
[TD]to[/TD]
[TD]7:30[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Shift Date[/TD]
[TD="colspan: 2"]10/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Lunch[/TD]
[TD]9:45[/TD]
[TD]to[/TD]
[TD]10:45[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Area[/TD]
[TD="colspan: 2"]A1[/TD]
[TD="colspan: 2"]Orderfilling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]2nd Break[/TD]
[TD]12:45[/TD]
[TD]to[/TD]
[TD]13:45[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Shift[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Time Gap[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Indirect Times[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 6"]Pick Gaps[/TD]
[TD] [/TD]
[TD="colspan: 2"]First Pick[/TD]
[TD]Last Act[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Associate[/TD]
[TD] [/TD]
[TD]Code[/TD]
[TD]Start[/TD]
[TD]Time[/TD]
[TD]Trip[/TD]
[TD] [/TD]
[TD]Trip[/TD]
[TD]Time[/TD]
[TD]Trip[/TD]
[TD]Time[/TD]
[TD]Gap[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Trip[/TD]
[TD]Time[/TD]
[TD]Trip[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1510"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]505[/TD]
[TD] [/TD]
[TD]ISTART [/TD]
[TD]4:00[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD] [/TD]
[TD]1974[/TD]
[TD]7:00[/TD]
[TD]1813[/TD]
[TD]7:21[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD] [/TD]
[TD]1886[/TD]
[TD]4:08[/TD]
[TD="align: right"]1787[/TD]
[TD="align: right"]13:34[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]IBREAK1 [/TD]
[TD]7:02[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD] [/TD]
[TD]2015[/TD]
[TD]10:01[/TD]
[TD]2015[/TD]
[TD]10:35[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]ILUNCH [/TD]
[TD]10:01[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD] [/TD]
[TD]1936[/TD]
[TD]12:45[/TD]
[TD]1787[/TD]
[TD]13:05[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]IBREAK2 [/TD]
[TD]12:54[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]IENDAY [/TD]
[TD]13:34[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


variable range is in cells O3 & Q3 (6:45 to 7:45)
break range is pulled to columns I & K
break gap difference is column L
threshold time is R3 (20 minutes)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You must log in or register to reply here.

Similar threads

J
  • Solved
Count Values in sequence
Replies
3
Views
360
myall_blues
myall_blues
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
270
tezza
tezza
D
  • Solved
ROUNDUP and MIN with IF/AND
Replies
6
Views
201
dreid1011
dreid1011

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

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
Back
Top