conditional formatting an expiry date by month and year

misstoffeepenny

New Member
Joined
Jan 18, 2021
Messages
4
Office Version
  1. 2016
  2. 2011
Platform
  1. Windows
hi all, I am trying to conditionally format a multi-date spreadsheet based on whether the month and year is passed this month and year.

eg. if a certificate is passed it's expiry date then highlight red, if its due to be renewed in the next month highlight yellow

I only have the cells formatted in mm/yy eg. feb-21
I have gotten the cells to format for the upcoming month but cannot get the expired dates to format.

can some help please?

note also some cells show n/a instead of a date as they do not apply to a particular site, I want these to have no formatting
1610990602276.png
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,944
Office Version
  1. 365
Platform
  1. Windows
Your screen capture is too small to read so I've based this on normal theory.

Where A1 contains the expiry date to be formatted, this formula as a rule should be able to highlight that cell when the current date has passes the end of the expiry month.
Excel Formula:
=IF(A1,EOMONTH(A1,0)<TODAY())
For posting samples, it is preferred that you use XL2BB, for which there is a link to a guide and download in my signature block below this post.
 
Solution

misstoffeepenny

New Member
Joined
Jan 18, 2021
Messages
4
Office Version
  1. 2016
  2. 2011
Platform
  1. Windows
thank you very much for your help, that was driving me mad for about 2 hours yesterday
 

misstoffeepenny

New Member
Joined
Jan 18, 2021
Messages
4
Office Version
  1. 2016
  2. 2011
Platform
  1. Windows
secondary question to this

if i now want the date to format green when a date in another column supercedes it how to i amend the formula or do i apply a seconday formula
eg. date is passed today format red
 

misstoffeepenny

New Member
Joined
Jan 18, 2021
Messages
4
Office Version
  1. 2016
  2. 2011
Platform
  1. Windows
sorry it posted it there

eg. date in cell H4 is passed today, format red, however if date in cell I4 is greater than H4 highlight green
so red date would be an action needing to be completed passed date due and green would be an action completed
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,944
Office Version
  1. 365
Platform
  1. Windows
It would need a second rule to be applied, although a rule can have multiple conditions it can only set one colour, so you need one for red and another for green.

For the green rule, try
Excel Formula:
=N(I4)>N(H4)
In the conditional format manager, check the order of the rules, If I remember correctly a newly added rule goes to the bottom by default, but if the new rule is above the old one in the list then the 'Stop if true' box needs to be checked.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top