Hi, I use a schedule sheet like the one in the photo. I would like it so when the times are input to the C column, the bars along the schedule are filled in automatically. Like the example in the photo

It will make life simpler if you change row 7 to the same format as column B. For example, in C7, replace "7-8" with "07:00-08:00". Then you can use a "common" conditional formatting formula for the bar cells. In C8, the formula would be AND(LEFT($B8,5)>=LEFT(C$7,5),RIGHT($B8,5)<=RIGHT(C$7,5)). You can copy this formula to all the bar cells. If you want different colors in the rows, you will need to adjust the action on a cell by cell basis.

Excel Fill schedule - Microsoft Q&A

Accepted answer

Barry Schwarz 2,511 Reputation points

2024-05-26T14:52:29.2333333+00:00

It will make life simpler if you change row 7 to the same format as column B. For example, in C7, replace "7-8" with "07:00-08:00".

Then you can use a "common" conditional formatting formula for the bar cells. In C8, the formula would be AND(LEFT($B8,5)>=LEFT(C$7,5),RIGHT($B8,5)<=RIGHT(C$7,5)).

You can copy this formula to all the bar cells. If you want different colors in the rows, you will need to adjust the action on a cell by cell basis.
Please sign in to rate this answer.
Harrison Allen 20 Reputation points

2024-05-26T15:24:04.26+00:00

Thanks for your reply! It seems that this is returning all the cells as false, no matter the value. I have formatted the times in the top row as suggested. Thanks !

Barry Schwarz 2,511 Reputation points

2024-05-27T12:38:08.8333333+00:00

The formula in my answer had the comparisons reversed. It should be LEFT(...)<=LEFT(...) and RIGHT(...)>=RIGHT(...).

Additionally, you don't copy the formula into the cells. That is the easiest way to test the formula. To produce the bars, you copy the formula into a conditional formatting rule.

The formula will need tweaking if you want to handle shifts that start or end in the middle of an hour.

If you want the rows to have different colors, you will need to create a rule for each row.
Sign in to comment

Share via

Excel Fill schedule

0 additional answers