Conditional formatting using months of dates

[DaSandmanCometh]

I've researched this and researched this and nothing is really working.

I would like to conditional format cells based on the month of the date entered into it.

For current month. example: MONTH(TODAY()), then it highlights Red
If it's next month. example: MONTH(TODAY()+1), then it highlights Orange
between 1 and 3 months out. example: MONTH(TODAY()+1<=Cell<=MONTH(TODAY()+3)

But for some reason I can't get anything to work other than =MONTH(C12)<=MONTH(NOW()) for the red

Thanks in advance for any help.

[FDibbins]

For this month...
=AND(C2>EOMONTH(TODAY(),-1),C2<=EOMONTH(TODAY(),0))
for next month...
=AND(C2>=EOMONTH(TODAY(),0),C2<=EOMONTH(TODAY(),1))
for 3 months and more...
=C2>EOMONTH(TODAY(),2)

Note, it may be better if you put =TODAY() in it's own cell, then referenced it. If used too much, that function could start to slow your file down

[DaSandmanCometh]

I inserted those in using formula and none are working. Maybe I'm doing something wrong, but I don't think so.

And would AND(C2>=EOMONTH(TODAY(),2),C2<=EOMONTH(TODAY(),3)) get me those months between 2 and 3 months out? I don't need 3 months and more, I need 2 and 3 months out on that last one.

[FDibbins]

I have adjusted the 3rd formula to look btw 2 and 3 mths.

I have copied your table over and applied my rules

[DaSandmanCometh]

Still doesn't look like its working as needed, some dates that should be highlighted are not and some that shouldn't are. Not sure where the disconnect is but I do appreciate your help.

[FDibbins]

Tell me which 1's you feel are wrong please

[DaSandmanCometh]

=AND(C2>=EOMONTH(TODAY(),1),C2<=EOMONTH(TODAY(),2))
And then this should highlight July and August. Correct? or does it need to be 2 and 3 in the formula?


I changed this to 2 and 3 and it worked on them all, so may have been some conflict the other way. But I can't format paint these into my original file. I paste a cell and use the format painter and it shows the conditional formatting but it's not highlighting anything. Even when I try to format paint them into the original portion in the file you posted they aren't working. Am I'm missing something? And could you help with one for months that have pasted?

[FDibbins]

The table below shows what date EOMONTH() would give from today's date, based on the "months" shown next to the dates

	S	T
3	30-Apr-18	-1
4	31-May-18	0
5	30-Jun-18	1
6	31-Jul-18	2
7	31-Aug-18	3

As you can see, using 2 (2 months ahead), gives end of July, so dates up to end July would qualify, which, if you look at my file that I sent you, is exactly what is happening. If you need to include Aug, you would need to adjust the "month" value

[DaSandmanCometh]

I got it working, I had messed up the cell reference, sorry for the trouble. Could you give me one to highlight past months?

[FDibbins]

As you can see from my table in post 8, you can use negative numbers to go backwards in months.
-2 would be Mar
-1 would be Apr
0 would be May
1 would be Jun
etc

See if you can work it out from that, if not, give me a shout and I will assist you further

[DaSandmanCometh]

I got it working, not sure if it's the most efficient way, but it's working. I can't post the formula as I get a warning saying it's html.

[FDibbins]

Yes, the forum firewall sometimes thinks you are posting HTML if you use < or > without a space either side of them.
Try posting the formula with spaces...
=AND(C2 > =EOMONTH(TODAY(),1),C2 < =EOMONTH(TODAY(),2))

[DaSandmanCometh]

=C2 < EOMONTH(TODAY(),-2)

This is what I figured out, it works.

[FDibbins]

Looks good to me, well done

[DaSandmanCometh]

THanks for the help