Conditional formating

R

Renee Cole

I have a spreadsheet containing training completion dates for numerous tasks
for each employee. Since the training is done annually, I want the cell to
turn red once the year expires so that the supervisors reviewing the
spreadsheet can notify the employee their next annual training is due.
 
M

Mike H

Hi,

Try this
Format|Conditionl format
select formula is
enter the formula
=A1<TODAY()-365
Mike
 
R

Renee Cole

Great! Now how do i copy this conditional format rule for 130 employees for
a given task without having to set up a new rule for each cell?
 
D

David Biddulph

Either select all the cells before you apply the CF,
or use the format painter,
or copy and Edit/ Paste Special/ Formats.
 
R

Renee Cole

Sorry to bug again, but I'm not sure I'm clear. So if i have 130 employees
with 30 different training task (all with a variety of dates completed), it
seems like in the formating formula that you have to select an individual
cell to assess a date. If I select all of the cells in just one row and then
go to formating, won't it select the criteria based on the date in the first
cell? Is there a way to say B5:CZ5? I was hoping to not format each
individual cell since most dates are different (3900 cells) for each employee.

Thanks again,
 
D

David Biddulph

Why don't you try it, Renee?

If you select a range of cells, and the active cell is A1, then if the CF
formula you type in is
=A1<TODAY()-365
you can look at the other cells and you'll find that in A2 the formula is
=A2<TODAY()-365
and in B1 the formula is
=B1<TODAY()-365

Similarly if you copy the CF, either by format painter or by paste special/
format, the same adjustment of cell references will apply.

If you had wanted all the cells to use a formula based on the date in the
first cell you would have used the formula
=$A$1<TODAY()-365
instead of
=A1<TODAY()-365

Look in Excel help at the difference between absolute ans relative
addressing.
 
Top