Formula for conditional format with Months

[twwareza]

I have a worksheet like this. The months is a date, but it shows only the month. I used Edate to add one month. 

I am not able to work out how to create the formula to enable the conditional formatting to work properly. ie highlights the months if it falls within the two dates. 

Any tips?

Start Date	No Days	End Date
			Mar	Apr
01/03/2021	10	11/03/2021
12/03/2021	4	16/03/2021
17/03/2021	4	21/03/2021

Thanks!

Reza

[#2 · Accepted solution]

@twwareza I solved your problem on live youtube video. Just watch the video now:

https://youtu.be/peVWO97dv38

[#3]

Hi big thanks. @jukapil 

I noticed you used endtext ...but the row of months are actually a date, and I just displayed the Month. Would this matter or not?

I will have a bash at this in the morning. Heading out now. 

Thanks 

[#4]

@twwareza then on the right side you need to replace D$2 with Text(D$2,"MMM")

[#5]

@jukapil great. This kind of got it to work but now a few issues.

As my months spans over 3 years it repeats the pattern across all years. 

Also - odd that when no start/end dates are given - it still formats the cell. 

This is the structure: 

=OR(TEXT($C6,"MMM")=TEXT(H$4,"MMM"),TEXT($E6,"MMM")=TEXT(H$4,"MMM"))

I guess its not the full solution to the problem. 

Thanks for the guidance. 

a. This picture shows the months repeated across all years

b. This one shows the cell formatted even if start-end date is empty

[#6]

@twwareza Yes you are right because initially I saw the Month name. What you need to do, just add MMMYY in the formula and it will work

[#7]

You star @jukapil 

That worked. 

Thanks again

[#8]

@jukapil 

Looks like when days span many months, it does not capture this with the current formula. This is a screenshot of what I mean.

I tried adding >= or <= but that did not work. 

Original

=OR(

TEXT($E7,"MMMYY")=TEXT(O$5,"MMMYY"),TEXT($G7,"MMMYY")=TEXT(O$5,"MMMYY"),

TEXT($H7,"MMMYY")=TEXT(O$5,"MMMYY"),TEXT($J7,"MMMYY")=TEXT(O$5,"MMMYY"),

TEXT($K7,"MMMYY")=TEXT(O$5,"MMMYY"),TEXT($M7,"MMMYY")=TEXT(O$5,"MMMYY")

)

Tweaked

=OR(

TEXT($E7,"MMMYY")>=TEXT(O$5,"MMMYY"),TEXT($G7,"MMMYY")<=TEXT(O$5,"MMMYY"),

TEXT($H7,"MMMYY")>=TEXT(O$5,"MMMYY"),TEXT($J7,"MMMYY")<=TEXT(O$5,"MMMYY"),

TEXT($K7,"MMMYY")>=TEXT(O$5,"MMMYY"),TEXT($M7,"MMMYY")<=TEXT(O$5,"MMMYY")

) 

Any thoughts how to fix this?

Reza