Conditional Format Current Month and Those Prior

[brenda1996]

Greetings! First I would like to say thank you for taking the time to look at my issue and offer a solution, your help is greatly appreciated. I am looking for help in creating a formula or direction on how to highlight dates in a cell(s) using Conditional Formatting for which are in the current month and those prior. Seems like a simply task but I cannot wrap my mind around getting there. I have tried EOMONTH, TODAY and DATE in various forms in formulas and am having no success, ready to throw in the towel. Thought I'd try here for some expert help. Thank you! Tina

 

[Joe4]

Welcome to the Board!

For a date in cell G1, the Conditional Formatting Formula would look like:

=G1<=EOMONTH(TODAY(),0)

 

[brenda1996]

Welcome to the Board!

For a date in cell G1, the Conditional Formatting Formula would look like:

=G1<=EOMONTH(TODAY(),0)

Oh my gosh, I can't believe I was not able to see that! So simple! It worked, thank you so much Joe! I can't thank you enough, have get day!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[brenda1996]

You are welcome.
Glad I was able to help!

Yes, the formula worked great however now I just noticed all the way to the end of my data (5000 lines) all empty cells are also colored. Would prefer them to stay default white. Have tried another format to change cell white for fill and text if error or zero value and are in order of preference but cells remain colored. Am I simply stuck with this? Thanks again for your help.

 

[Joe4]

Just add a condition to see if there is a value in G1, i.e.

=AND(G1>0,G1<=EOMONTH(TODAY(),0))

 

[brenda1996]

Just add a condition to see if there is a value in G1, i.e.

=AND(G1>0,G1<=EOMONTH(TODAY(),0))

Wow! You are awesome! I wish I had your knowledge. Worked just as you laid it out. Going to study it now to learn how it exactly works. Thanks again for helping me keeping Excel fun! Brenda

 

[Joe4]

You are welcome.

See if this helps you understand it: https://support.office.com/en-us/ar...nditions-0084efde-35e7-41aa-8909-2fa0c101ae0c

If you still have any questions on the formula, or how it works, feel free to ask here.