mads
|
| posted on 24/1/13 at 01:49 PM |
|
|
conditional formatting in excel
Hi all,
Can anyone advise how best to setup the conditional formatting for the following scenario please?
Column A has list of users
Column B has the date they last did training X
Column C has the date they last did training Y
etc etc
Training X is due for renewal 2years from the date it was last done
Training Y is due for renewal 7 months from the date it was last done
etc etc
At specified time periods from when the training was done, I want the colour of the box to change to visually highlight that it is either a) still far
off b)coming up to renewal c) due very soon d)overdue (so green, amber, red, dark red)
I cant work out what rule I need put in the conditional formatting though.
Anyone help please? Using Office 2007 if it makes any difference.
Thanks,
mads
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
stevebubs
|
| posted on 24/1/13 at 03:37 PM |
|
|
Put in a "colour scale " conditional formatting with
minimum set as a formula ( =today()-720) )
maximum set as a formula ( =today() )
Stephen
[Edited on 24/1/13 by stevebubs]
|
|
|
stevebubs
|
| posted on 24/1/13 at 03:38 PM |
|
|
The above is for office 2010; 2007 may be slightly different...
|
|
|
stevebubs
|
| posted on 24/1/13 at 03:43 PM |
|
|
Walked to work laptop (office 2007)
Exactly the same. U2U me your email and I'll send you an example if you want.
S
|
|
|
mads
|
| posted on 24/1/13 at 04:11 PM |
|
|
Thanks Stephen. What if I want three or four distinct colours rather than a scale though?
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
Slimy38
|
| posted on 24/1/13 at 04:48 PM |
|
|
You can 'stack' conditional formats, so instead of scales you have solid colour for each range. Just keep repeating the process.
|
|
|
