Highlight the current or past month in Excel with conditional formatting
Q: In a worksheet containing many columns showing dates as just a Month and Year, I want to use Conditional Formatting to show the current month in a distinct style, (e.g. Red Bold font) so that they stand out.
How can I specify the 'Current Month' in the Conditional Formatting box Please?
A: Nice question. You need to show the conditional formatting as 'Formula' rather than cell value, then type the following formula in the box:
=DATEVALUE(DAY(TODAY())&"/"&MONTH(A1)&"/"&YEAR(A1))=(TODAY())
This is based on my European date style. For a North American installation, you might have to reverse the position of "DAY(TODAY())" and "MONTH(A1)".
"A1" of course is the assumed first cell of the range to take the conditional format.
Q: Is there a way I can format a cell in Excel to show text in a distinct style, if the month is BEFORE the current month?? You kindly told me how to make the CURRENT month show up in a different way, and using your formula I have tried to make this subtle change but without success.
A: This should be pretty straightforward. Replace the second '=' sign with a '<' sign, thus:
=DATEVALUE(DAY(TODAY())&"/"&MONTH(A1)&"/"&YEAR(A1))<(TODAY())
Remember that CF gives you the opportunity to test up to 3 conditions, so you could use both formulae and highlight past months red and the current month blue, for instance.
Or if you want just the immediately preceding month to be highlighted, then use this formula:
=DATEVALUE(DAY(TODAY())&"/"&MONTH(A1)+1&"/"&YEAR(A1))=(TODAY())