conditional formatting formula for cell value of previous month and keep clear for blank

[eugz]

Hi All
I created conditional formatting:
CF_date.png
Right now the column D is blank but all cells in the column color
How create conditional formatting to color only if date value in the column is previous month or early?

Thanks

[kvsrinivasamurthy]

Try this

=AND(YEAR($D2)<=YEAR(TODAY()),MONTH($D2)<MONTH(TODAY()))

[eugz]

Hi kvsrinivasamurthy. Thanks for reply.
Your conditional formatting has the same problem like and my. It continue fill coloring cell if cell blank also. I try to formatting only if cells have value.

Thanks

[HansDouwe]

Please try =AND(D2>0,D2<DATE(YEAR(D2),MONTH(D2),1))

[kvsrinivasamurthy]

Try

=AND($D2<>"",YEAR($D2)<=YEAR(TODAY()),MONTH($D2)<MONTH(TODAY()))

[HansDouwe]

@kvsrinivasamurthy

I think your formula doesn't always work well.
If D2 = December 15, 2021 then the formula returns FALSE,
because the month of December is not smaller than today's month (September),
but December 2021 is still ahead of today.

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

If result is not ok then, pl upload sample file showing the required results.

[eugz]

Hi HansDouwe. Thanks for reply.
Your formula remove color for blank cells but it also remove color from cells with date value earlier last month. For instance, if date value 8/1/2022 a cell has color, but if cell value 7/31/2022 or earlier condition doesn't work. I used for last month condition rule: "Format only cells that contain", "Date Occurring", and "Last month". My problem create formula condition for date value earlier then last month and remine not color cells with blank value.

Thanks

[Gregb11]

Try:

=AND($D2<=EOMONTH(TODAY(),-1),$D2<>"")

[HansDouwe]

Hi HansDouwe. Thanks for reply.
Your formula remove color for blank cells but it also remove color from cells with date value earlier last month.
Thanks

My formula is not meant to remove color, my formula is meant to set the color right away.
My formula should take the place of your formula, but not next to it.
If you upload an example sheet I'll show you that.