Ask a new question

Conditional Formatting

Hi I need to use conditional formatting to highlight dates in a column so that two months before the date occurs the text changes colour to e.g.yellow and once the date has passed, i.e it is yesterday or earlier the text changes to red.  I have found what looked like some help but it was for a different build of office and I could not follow it in 365 which is what I use. 

The spreadsheet is a list of companies and their quality criteria which must be updated either every two years or by the expiration date of the existing approvals.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Can you provide some more details?
What are the cells with the date?
Is it 2 months or more before the date that you want the color to be yellow, or once the date is within two months from the current date to be yellow?
And once the current date has passed the date in the column, it will turn red?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Sorry Jason I am not sure that I understand your first question but in essence each row would run across - columns with the name and address of a company , the company contact etc, then the types of quality approvals they have e.g ISO 9001 and then a column with the expiration date of the quality approval e.g September 18th 2018.  If I were looking at this spreadsheet today then I want this date to be in the normal black font (same as the rest of the spreadsheet).  However if I were to look at that spreadsheet between July 20th and September 17th 2018 then I would like the colour of the font to be different e.g. amber in colour.  If I were to look at that same date on September 18th 2018 or later then I would like the font to be red.

The idea of this is an easily visible warning to get updated information from the company concerned ideally before their current quality approval expires.  I do hope that I have properly clarified what I am trying to do

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

How do you define "a month", is just a difference between month, ie today is "1 month" from May 1'st? Or is it 28 (4 weeks), 30 or 31 days?
.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Are you able to save a copy to OneDrive and post the link here?

I can then open it up and do the conditional formatting for you.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

As far as the spreadsheet is concerned it does not matter exactly how a month is defined.  It is just a reasonable period of approximately two months to give sufficient time to get updated paperwork - something around 56 - 60 or so days is fine, the exact amount of time is not critical. 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Jason, I could probably do so although at present the spreadsheet is a work in progress and has not yet been completed. That is a very kind offer.  However would you also be able to explain what you have done as this is something I would like to understand so that I could do it myself in the future? 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

. * Make sure the dates are in a date format
. * Select the column
. * go to Home tab > Styles group > Conditional formatting drop down> Highlight Cells Rules > between
. * in the first input area enter =today()+30
. * in the second input area enter =today()+60
. * select the formatting, ie light red fill ...
. * click OK to save
. * go back ... > Conditional Formatting drop down > Highlight Cells Rules > greater than
. * enter =today()+30
. * select formatting, ie light yellow fill ...
. * click OK to save

You have to put them in that order so that red is applied before yellow.
.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Oops, I see I got the colors backwards, same idea just change the colors selected
.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Actually on further thought you might want to make the first 30 days, the "serious" short term also a between, ie

. * go back ... > Conditional Formatting drop down > Highlight Cells Rules > between
. * in first input enter =today()
. * in second input enter =today()+30
. * select formatting, ie light red fill ...
. * click OK to save


How do you want to handle rows that have gone past due?

Maybe you want to make it a 3 level warning, green for 30-60 days, yellow for 0-30 days and red for past due?
.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Assume your dates are in column A starting at A2.
Select the range of dates.
Conditional formatting->New rule->Use a formula
Enter the formula =A2<=TODAY()+60 and format the cell as yellow.
Now repeat, using the formula =A2<=TODAY() and format as red.

Note: the order in which you do these two steps is important.
Regards

Murray
https://excel.dadsonion.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info

Last updated October 5, 2021 Views 24 Applies to: