SOLVED

Formula for conditional format with Months

%3CLINGO-SUB%20id%3D%22lingo-sub-2070479%22%20slang%3D%22en-US%22%3EFormula%20for%20conditional%20format%20with%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070479%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20like%20this.%20The%20months%20is%20a%20date%2C%20but%20it%20shows%20only%20the%20month.%20I%20used%20Edate%20to%20add%20one%20month.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20am%20not%20able%20to%20work%20out%20how%20to%20create%20the%20formula%20to%20enable%20the%20conditional%20formatting%20to%20work%20properly.%20ie%20highlights%20the%20months%20if%20it%20falls%20within%20the%20two%20dates.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20tips%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22449%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2295%22%3EStart%20Date%3C%2FTD%3E%3CTD%20width%3D%2284%22%3ENo%20Days%3C%2FTD%3E%3CTD%20width%3D%2296%22%3EEnd%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2284%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EMar%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EApr%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295%22%3E01%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%2284%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2296%22%3E11%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295%22%3E12%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%2284%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2296%22%3E16%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295%22%3E17%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%2284%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2296%22%3E21%2F03%2F2021%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CBR%20%2F%3EThanks!%3CBR%20%2F%3E%3CBR%20%2F%3EReza%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2070479%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2070552%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20conditional%20format%20with%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915784%22%20target%3D%22_blank%22%3E%40twwareza%3C%2FA%3E%26nbsp%3BI%20solved%20your%20problem%20on%20live%20youtube%20video.%20Just%20watch%20the%20video%20now%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FpeVWO97dv38%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FpeVWO97dv38%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2070851%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20conditional%20format%20with%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070851%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20big%20thanks.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F476378%22%20target%3D%22_blank%22%3E%40jukapil%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20noticed%20you%20used%20endtext%20...but%20the%20row%20of%20months%20are%20actually%20a%20date%2C%20and%20I%20just%20displayed%20the%20Month.%20Would%20this%20matter%20or%20not%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20have%20a%20bash%20at%20this%20in%20the%20morning.%20Heading%20out%20now.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2070943%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20conditional%20format%20with%20Months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915784%22%20target%3D%22_blank%22%3E%40twwareza%3C%2FA%3E%26nbsp%3Bthen%20on%20the%20right%20side%20you%20need%20to%20replace%20D%242%20with%26nbsp%3BText(D%242%2C%22MMM%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 DateNo DaysEnd Date
   MarApr
01/03/20211011/03/2021  
12/03/2021416/03/2021  
17/03/2021421/03/2021  


Thanks!

Reza

7 Replies
Best Response confirmed by twwareza (Occasional Contributor)
Solution

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

 

https://youtu.be/peVWO97dv38

 

 

I help people based on their questions and these are random videos. Hope it helped the user.If you wanna support us and want us to write more. You can buy us...

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 

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

 

 

@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

Screenshot 2021-01-18 at 11.35.13 PM.png

 

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

Screenshot 2021-01-18 at 11.34.12 PM.png

@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

You star @jukapil 

 

That worked. 

Thanks again

@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?

RezaScreenshot 2021-01-20 at 1.50.23 PM.png