Forum Discussion
Conditional Formatting using months instead of days
Hey Everybody,
I work for an airline and for our due date tracking we use calendar months for due dates. With these calendar month due dates, you have a three month "grace" window to complete the event. For instance if you had a check due in Feb 2020, you would have a three month window of Jan, Feb and Mar to get the event done.
So I have the dates in an excel spreadsheet and would like to make it so that the cell turns green the month prior, yellow the month of and red the month after.
I am having trouble with the conditional formatting though because I cant figure out how to get the date, such as Feb 2020 to register as an integer (1-12) using the MONTH function.
For the record I am not sure if this is the best way, but it seems it would be easiest if I could get the month to register as a number and just do the conditional formatting to the current month +1 or -1.
Ive attached the spreadsheet that I currently have for reference.
Also, I am using Excel for Mac 2008
Thank you!
2 Replies
Hello Conquest441,
As an alternative to the answer provided by mathetes, you could use the following (not so elegant) conditional formats:
Starting in cell B4 of your table:
RED
=IFERROR(-DATEDIF(EOMONTH(B4,-1)+1,EOMONTH(TODAY(),-1)+1,"M"),DATEDIF(EOMONTH(TODAY(),-1)+1,EOMONTH(B4,-1)+1,"M"))=1
YELLOW
=IFERROR(-DATEDIF(EOMONTH(B4,-1)+1,EOMONTH(TODAY(),-1)+1,"M"),DATEDIF(EOMONTH(TODAY(),-1)+1,EOMONTH(B4,-1)+1,"M"))=0
GREEN
=IFERROR(-DATEDIF(EOMONTH(B4,-1)+1,EOMONTH(TODAY(),-1)+1,"M"),DATEDIF(EOMONTH(TODAY(),-1)+1,EOMONTH(B4,-1)+1,"M"))=-1
There was another query very similar to yours not long ago. That other one had to do with emergency medical training (CPR, etc) that could expire. The inquirer wanted dates to turn yellow at a certain point, red at another, just as you're asking.
I created the attached spreadsheet to illustrate how the Conditional Formatting rules could be written, referring both to the TODAY() function AND to a separate table to control those trigger points at which the subject cells would turn yellow or red or whatever you wanted.
It sounds like you're quite adept with Excel, so I'm going to let you adapt this example to your own situation. Feel free to come back with questions, though, if you have them.
