98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - "if" with conditional formatting
Excel - "if" with conditional formatting
Resolved · Urgent Priority · Version 2019
Mon 15 Mar 2021, 08:23Delegate Lindsey said...
Lindsey has attended:
Excel Intermediate course
Excel - "if" with conditional formatting
I have a spreadsheet with 2 date columns. I need the dates to highlight for the following reasons:
1 date column needs to highlight if the expiry date is within 1 month to amber, more than 1 month in red, and prior to expiry green.
the other date needs to highlight if the date passes in red. How can I do this?
Mon 15 Mar 2021, 14:37Trainer Marius said...
RE: Excel - "if" with conditional formatting
Hello Lindsey,
Thank you for your question. Just so that we understand your data: are the expiry dates future or past dates in relation to current date?
Also, please could you clarify "prior to expiry"? What would be the time frame / cut-off here?
How do the dates in the second column relate to the dates in the first column?
It would be very helpful if you could send us a small sample spreadsheet for clarity. It will help us to give you the best solution.
Please send it to info@stl-training.co.uk
Many thanks
Marius Barnard
STL
Mon 15 Mar 2021, 14:59Delegate Lindsey said...
RE: Excel - "if" with conditional formatting
Hi Marius, thank you for the response. I have sent the spreadsheet as requested to the email address, with the following clarifications.
The column for public liability insurance I need the dates to highlight green if in date, amber 1 month prior to the date and red the day after to show expired.
The column for PQQ date I would like to show red after 1 year has lapsed from the date.
Thanks in advance
Mon 15 Mar 2021, 15:33Trainer Marius said...
RE: Excel - "if" with conditional formatting
Hi Lindsey,
Thanks for sending the file. I do apologise for being slightly obtuse regarding this data, and I'm sure you're thinking 'How hard can it be?', but insurance is not my field and I'm still not sure about the meanings of 'all in date', 'within month of renewal' and 'expired'.
I'm guessing we are comparing dates from columns C and D, but I need to know how.
Please could you bear with me and post in this forum chat, an example of each, with sample dates to compare?
I'm sure that it's very simple and I will eventually get the picture.
Sorry to be a pain!
Kind regards
Marius
Mon 15 Mar 2021, 15:49Delegate Lindsey said...
RE: Excel - "if" with conditional formatting
Hi Marius,
No problem at all - I do not work in insurance either haha.
Ok so the insurance dates are the dates that that companies insurance runs out. So D4 for example shows 6/11/2020 as the expiration date, so that insurance is now expired so should show red.
D16 date is 13/06/2021 so their insurance is still in date so should be green.
D89 date is 21/03/2021 so as we are 15/03/2021 today, this insurance will expire within 1 month so needs to be amber.
Column C is slightly different, any date that is 1 year or older needs to show red, anything less than a year of today's date needs to be green.
I hope that makes a bit more sense?
Mon 15 Mar 2021, 15:54Trainer Marius said...
RE: Excel - "if" with conditional formatting
Hi Lindsey,
Thanks, this is very clear now. I will create and test some rules and then post them in the forum for you. I just have a call in a few minutes so it might be a while until I post.
Kind regards
Marius
Mon 15 Mar 2021, 15:58Delegate Lindsey said...
RE: Excel - "if" with conditional formatting
Brilliant, that would be amazing, thank you so much
Mon 15 Mar 2021, 17:03Trainer Marius said...
RE: Excel - "if" with conditional formatting
Hi Lindsey,
Here are my solutions. I hope they are what you need. They work for me.
You need to create each rule below separately. Please type all the formulas precisely as shown.
1. For Column D's GREEN dates: Select all the dates, then click Home - Conditional Formatting - New Rule - Use a formula to determine which cells to format.
Type the following formula into the box:
=$D4>EDATE(TODAY(),1)
Click Format (bottom right), Fill, pick the relevant colour, click OK, OK
2. For Column D's AMBER dates: Select all the dates, then click Home - Conditional Formatting - New Rule - Use a formula to determine which cells to format.
Type the following formula into the box:
=AND($D4>=TODAY(),$D4<=EDATE(TODAY(),1))
Click Format (bottom right), Fill, pick the relevant colour, click OK, OK
3. For Column D's RED dates: Select all the dates, then click Home - Conditional Formatting - New Rule - Use a formula to determine which cells to format.
Type the following formula into the box:
=$D4<TODAY()
Click Format (bottom right), Fill, pick the relevant colour, click OK, OK
4. For Column C's RED dates: Select all the dates, then click Home - Conditional Formatting - New Rule - Use a formula to determine which cells to format.
Type the following formula into the box:
=$C4<=EDATE(TODAY(),-12)
Click Format (bottom right), Fill, pick the relevant colour, click OK, OK
5. For Column C's GREEN dates: Select all the dates, then click Home - Conditional Formatting - New Rule - Use a formula to determine which cells to format.
Type the following formula into the box:
=AND($C4>EDATE(TODAY(),-12),$C4<=TODAY())
Click Format (bottom right), Fill, pick the relevant colour, click OK, OK
I know it's a lot of steps but it's so worth it!
Kind regards
Marius
Mon 22 Mar 2021: Automatically marked as resolved.
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. |
Excel tip:Viewing Many WorksheetsIf there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen - Right click on the navigation arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds. |
