Formula for Shading? Is this possible?

  • Thread starter Thread starter brianswann
  • Start date Start date
B

brianswann

I have been tranforming all of our paperwork to excel making things
easier and saving time. I am in the middle of a scheduling chart that
I want to make. I am trying to find out if there is a way to creat a
bar graph, representing what hours employees work so that you can view
your crew list and know where your shortages are with a quick glance.

I have most of the schedule written but here's what I need help with if
this is possible.

If a cell is equal to or greater than a # and less than another # shade
this cell in.

I want to enter hours that an employee works, let's say 9 am to 5 pm,
and I want the cells corresponding with that employee's name to
automatically shade in between the hours of 9 am and 5 pm. When you do
this for all of your employees you can glance at the chart for say 11
am, and see how many peole are scheduled.

I would appreciate any help. I've been searching for this for a while
before I thought to look for an excell forum.
 
Format>Conditional Formatting. Use Formula Is instead of Cell Value.
=AND(A2>Criteria,B2<Criteria) and Format to show the change if TRUE..
 
I want to enter hours that an employee works, let's say 9 am to 5 pm,
and I want the cells corresponding with that employee's name to
automatically shade in between the hours of 9 am and 5 pm. When you do
this for all of your employees you can glance at the chart for say 11
am, and see how many peole are scheduled.
Click to expand...

Use Conditional Formatting. If you put the employee name in column A,
the start time in column B, the finish time in column C, and the hours
(I used 24 hour format because it's easier) in columns D - AA (that's
24 hours), with headers in row 1 (the significant headers being the
hour headers, so D1 contains 1, E1 contains 2, etc.), then use this
formula in cell D2 for conditional formatting, and choose a format for
working hours:

=AND(D$1>=$B2,D$1<=$C2)

Then fill D2 across to AA2, and fill D2:AA2 as far down as you need
to.

Conditional Formatting is found under the Format menu.

Maybe this is a clearer representation of how I set up the table:

A B C D E F ...
1 name start finish 1 2 3
2 Brian 9 17
3 Sally 8 16
4 Ed 9 12
..
..
..
 
Back
Top