Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Conditional Formatting
Resolved · High Priority · Version 2007
Reply Fri 12 Aug 2011, 16:25Delegate Sandra said...
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
For upcoming training course dates see: Pricing & availability
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
Tue 23 Aug 2011: Automatically marked as resolved.
|