Highlight cells if using excel for scheduling

ajc623

Board Regular
Joined
Nov 8, 2013
Messages
51
These boards have been great in the past and I am hoping they can help me again. We have an excel sheet we use to create a daily work schedule for a group of employees where we have broken their day into 15 minute increments (each row) so they know what to work on and we know what is realistic to get done in the day.

For this example in cell C2 (6:45am) through C41 (4:30pm) we have the time of day and the in cells D2 through D41 we have the schedule for AL, this repeats in additional columns to the right. During the day various people perform jobs where they place a part on or in various machines and can leave for up to two hours with different parts taking different amounts of time. When making the schedule each day it has become more difficult to keep track of the timing and not "double book" of piece of equipment.

What we would like to have happen is if AL is making part 1 which needs two hours on machine 1 that if he starts it at 8am (cell D7) that it would highlight or some other visual way show that that part would be done at 10am (cell D15). We would want this to work for a wide range of items and be easy to update and add products.

Example of something that would work

Excel schedule.jpg


Hope this makes sense
Andrew
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
748
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi
Try this approach using conditional format

Project Plan R2.xlsx
ABCDEF
1Start Time =08:00FredBarneyWilmaBetty
2Duration Hrs00:3000:4501:3000:45
3Time Start08:1508:4509:3011:00
4Work itemsTime Finish08:4509:3011:0011:45
508:00
6Pour Part 108:15
708:30
8Pour Part 208:45
909:00
1009:15
11Pour Part 309:30
1209:45
1310:00
1410:15
1510:30
1610:45
17Pour Part 411:00
1811:15
1911:30
2011:45
2112:00
Sample
Cell Formulas
RangeFormula
D3:F3D3=+C4
C4:F4C4=IF(C2="","",+C3+C2)
B5B5=+B1
B6:B21B6=+B5+"0:15"
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5:C21Expression=AND($B5>=$C$3,$B5<=$C$4)textNO
E5:E21Expression=AND($B5>=$E$3,$B5<=$E$4)textNO
D5:D21Expression=AND($B5>=$D$3,$B5<=$D$4)textNO
F5:F21Expression=AND($B5>=$F$3,$B5<=$F$4)textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,282
Members
414,440
Latest member
Kim0204

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