Printable Version | Subscribe | Add to Favourites
New Topic New Poll New Reply
Author: Subject: conditional formatting in excel
mads

posted on 24/1/13 at 01:49 PM Reply With Quote
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!"

View User's Profile Visit User's Homepage View All Posts By User U2U Member
stevebubs

posted on 24/1/13 at 03:37 PM Reply With Quote
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]

View User's Profile View All Posts By User U2U Member
stevebubs

posted on 24/1/13 at 03:38 PM Reply With Quote
The above is for office 2010; 2007 may be slightly different...
View User's Profile View All Posts By User U2U Member
stevebubs

posted on 24/1/13 at 03:43 PM Reply With Quote
Walked to work laptop (office 2007)

Exactly the same. U2U me your email and I'll send you an example if you want.

S

View User's Profile View All Posts By User U2U Member
mads

posted on 24/1/13 at 04:11 PM Reply With Quote
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!"

View User's Profile Visit User's Homepage View All Posts By User U2U Member
Slimy38

posted on 24/1/13 at 04:48 PM Reply With Quote
You can 'stack' conditional formats, so instead of scales you have solid colour for each range. Just keep repeating the process.
View User's Profile View All Posts By User U2U Member

New Topic New Poll New Reply


go to top






Website design and SEO by Studio Montage

All content 2001-16 LocostBuilders. Reproduction prohibited
Opinions expressed in public posts are those of the author and do not necessarily represent
the views of other users or any member of the LocostBuilders team.
Running XMB 1.8 Partagium [ 2002 XMB Group] on Apache under CentOS Linux
Founded, built and operated by ChrisW.