Sandra has attended:
No courses
Conditional Formatting
Hi
I want to be able to colour code (RAG status) dates so that when we are close to an expiry date it flags it as amber or red.
I have a spread sheet with dates in and want to compare them to the current date ie the date the spreadsheet is looked at. The dates in the sheet are all future dates at the moment but I want to be able to code as follows:
- Red = expiry date due within 2 days or less
- Amber = expiry due within the next month
- Green = expiry due greater than 1 month
I have looked in conditional formatting but cant get 3 rules to apply to each cell. Which is what is required.
Can you help please?
Sandra
RE: Conditional Formatting
Hello Sandra,
Thank you for your question regarding Conditional Format.
Your 3 conditions would be as follows:
Select the area you want formatting to be applied. In conditional formatting, select New Rule, select second option, Format Only Cells that Contain..
Choose Cell Value Less than or Equal to... then type in the following, =today()+2
Set the format to Red
Repeat the above for the other 2 conditions, add new etc..
for amber, it would read Less than or Equal to.. =today()+28
for green, Cell value Greater than or equal to... =today()+28
Make sure the 3 conditions are in the correct order from top to bottom, Red condition (top), Amber, Green (bottom).
If you are new to the function of =today() it returns the current day!
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
