Formatting cell depending on month

[nickburt]

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.

 

[Joe4]

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):

=ISEVEN(MONTH(A2))

and then just use ISODD for the odd months.

 

[nickburt]

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.

 

[Joe4]

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.

=AND(A2>0,ISEVEN(MONTH(A2)))

 

[nickburt]

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.

 

[nickburt]

Am I setting the rule description correctly?

Cell value equals =AND(A2>0,ISEVEN(MONTH(A2)))

 

[Joe4]

You have to use the "Use a formula to determine which cells to format" option, not the "Cell value equals" option.

 

[nickburt]

Ah, yes, of course, many thanks. All works now for the cells with dates in. Thank you.

 

[Joe4]

You are welcome.