Formatting cell depending on month

nickburt

New Member
Joined
Dec 1, 2015
Messages
12
Hi folks,

I have a sheet that has various columns of dates and payments on those dates, grouped in weeks across the sheet.
As each payment is made, I change the date to show the date the payment was made.

As a quick visual aid ref, I change the highlight colour of the date cell to one of two colours (light green or yellow) so I can see quickly if a payment has been made this month, or if the last payment was last month. So as payments are made during the month, the colour changes across the sheet and I can quickly see what's due in the next few days, based on the dates of last months payments. If that all makes sense.

Dawned on me that I should be able to get conditional formatting to do it automatically for me.
So, I'd like the cell to change to one colour if the month is even and another colour if odd. eg, yellow for even month and green for odd month.

But I'm having a dumb moment and can't figure out the formula to put in the "highlight cells" "equal to"

I have the cells formatted to custom date, to show only dd/mm

Many thanks in advance.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
As long as your date column is really entered as a Date (formatting does not matter), you can use this Conditional Formatting formula for the even months (for an date entry in cell A2):
Code:
=ISEVEN(MONTH(A2))
and then just use ISODD for the odd months.
 
Upvote 0
Thanks for the quick reply, I had tried that, but it does the reverse.
If the cell is empty, it highlights it, if it has a date in it, it removes the highlight.
 
Upvote 0
If you have a date cell with a blank in it, check to see that it has an entry.
You can do that by adding another condition to the CF formula, i.e.
Code:
=AND(A2>0,ISEVEN(MONTH(A2)))
 
Last edited:
Upvote 0
Thanks, none of the date cells would normally be blank, so I shouldn't need to do a check.

But still doesn't highlight the cell with an even month entry in it and still highlights when empty.
 
Last edited:
Upvote 0
You have to use the "Use a formula to determine which cells to format" option, not the "Cell value equals" option.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top