Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating
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. -- R Cole |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating
Hi,
Try this Format|Conditionl format select formula is enter the formula =A1<TODAY()-365 Mike "Renee Cole" wrote: 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. -- R Cole |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating
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? -- R Cole "Mike H" wrote: Hi, Try this Format|Conditionl format select formula is enter the formula =A1<TODAY()-365 Mike "Renee Cole" wrote: 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. -- R Cole |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating
Either select all the cells before you apply the CF,
or use the format painter, or copy and Edit/ Paste Special/ Formats. -- David Biddulph "Renee Cole" wrote in message ... 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? -- R Cole "Mike H" wrote: Hi, Try this Format|Conditionl format select formula is enter the formula =A1<TODAY()-365 Mike "Renee Cole" wrote: 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. -- R Cole |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating
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, -- R Cole "David Biddulph" wrote: Either select all the cells before you apply the CF, or use the format painter, or copy and Edit/ Paste Special/ Formats. -- David Biddulph "Renee Cole" wrote in message ... 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? -- R Cole "Mike H" wrote: Hi, Try this Format|Conditionl format select formula is enter the formula =A1<TODAY()-365 Mike "Renee Cole" wrote: 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. -- R Cole |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating
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. -- David Biddulph "Renee Cole" wrote in message ... 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, -- R Cole "David Biddulph" wrote: Either select all the cells before you apply the CF, or use the format painter, or copy and Edit/ Paste Special/ Formats. -- David Biddulph "Renee Cole" wrote in message ... 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? -- R Cole "Mike H" wrote: Hi, Try this Format|Conditionl format select formula is enter the formula =A1<TODAY()-365 Mike "Renee Cole" wrote: 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. -- R Cole |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating | Excel Discussion (Misc queries) | |||
Conditional formating | Excel Worksheet Functions | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Worksheet Functions |