Conditional formatting with custom number??

[Deb James]

I thought this would be simple...

Each cell will have a number from 1-24 and an alpha character (ie: A, V, H,
F). The number designates the time each person starts work and the letter
designates where they are supposed to work.

What I want is all the Day Shift people with one type of background, the PM
shift people with a second background and the Night shift people with a
third background. Then I want to count how many people are scheduled per
shift per day.

I have already done this individually setting up each cell, but that was
before I learned about conditional formatting.
If conditional formatting can be made to work it would be much easier to
move people around the schedule.

I tried setting the cell as a custom number (0;;@) because of the alpha
character. I wanted to be able to conditional format each cell using the
number to make the different backgrounds.

This is easy to do with just a number. I thought about having 2 cells (one
with time, the other with location), but then moving them about gets tricky.
I would prefer the information be in one field.

I tried in Conditional Formatting to do: cell is <11@, cell is <11;@, etc.
I have tried lots of combinations and can't make it do a conditional format
with the letter. I don't think I can substitute the @ for "text" cause the
text changes.

Any ideas? I have been reading articles and trying combinations for days
and am stumped.

Thanks for any help,.
Deb

 

[Debra Dalgleish]

It will be easier to add conditional formatting if you use a two digit
number for each hour, e.g. 01A, 02V, 23H, 24F

Then, select the cells with the codes
Fill all the cells with the colour for the Night shift
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=LEFT(B2,2)+0<11
Click Format, and on the Patterns tab, select a colour for the Day shift
Click OK, click Add
In Condition Two, from the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=LEFT(B2,2)+0<19
Click Format, and on the Patterns tab, select a colour for the PM shift
Click OK, click OK