Shaun has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Effective Communication Skills course
Conditional format
I have a table of dates relating to when individuals qualifications expire. I want to format the cell with the dates in as follows, if expiry date is in the past flag cell red, if the date is within the next 30 days flag amber and if the cell is further than 30 days from today then flag green. I have tried but cannot achieve this- can you help.
Thanks
Shaun
RE: Conditional format
Hi Shaun,
Thank you for your question.
You want to create 3 separate rules for the 3 types of date.
Highlight your column with the dates.
Click "Conditional Formatting", then click "New Rule". In the new window, click "Use a formula to determine which cells to format." The first rule (more than 30 days in the future) will look something like:
=A1>TODAY()+30
where you can replace the "A1" with any cell in your column of dates.
You enter this into the empty field further down.
After this, click "Format", then select a green fill.
For dates between today and 30 days from today, follow the same steps, but use the following formula:
=A1>=TODAY()
and choose an amber fill.
For dates before today, follow the same steps, but use the following formula:
=A1<TODAY()
and choose a red fill.
Make sure the rules sit in the following order:
the "green" rule at the top, the "amber" rule second and the "red" rule at the bottom of the list of rules.
I hope this helps.
Kind regards
Marius Barnard
Excel Trainer
