Conditional formatting is inprecise

[AlexisLyonnais]

Hi everyone! I'm having trouble with conditional formatting in Excel. I basically achieved what I wanted but the result isn't exactly what I wanted and could lead to mistakes for me and my team. I am using Excel 2016 on Windows 10.

Here's my situation.

I have a column full of dates (Column L in "DATES" sheet) and I need those cells to change color if:

The date in the cell and today's date are both between this year's January 1st and mid-August;

OR;

The date in the cell and today's date are both between this year's mid-August and December 31st.

The references for January 1st, mid-August, December 31st and today's date are in the "REF" sheet.

I did a lot of testing and found a way to make this all work in a test sheet. But when I try to apply the same fomulas to my actual document, most of the right dates are formatted as I needed them to but a few are not, and a few that shouldn't be are.

Can anyone tell me what is doing this and how to fix it? Thanks a lot!

P.S. If anything isn't clear, please tell me, this is my first post here and English isn't my native language.

[#2]

@AlexisLyonnais 

You probably selected the wrong cell when creating the rule, for the conditional formatting formulas refer to different rows.

Select column L, delete the existing rules and replace them with a single rule with the following formula:

=OR(AND(L1>=REF!$B$1,L1<=REF!$B$2,REF!$B$4>=REF!$B$1,REF!$B$4<=REF!$B$2),AND(L1>REF!$B$2,L1<=REF!$B$3,REF!$B$4>REF!$B$2,REF!$B$4<=REF!$B$3))

I modified the formulas on the REF sheet slightly - your formula would return the 30th of December as the last day of the year in a leap year.

See the attached version.

[#3]

Thanks a lot! That did it! I'm learning as I go but needed that last little push!

Thanks again!