IF,AND,OR

[dlcartin]

I have the TODAY() function in cell C1 set to show the numerical value of the MONTH (1 - 12) and the same for C2 set to show the numerical value of the DATE (1 - EOM (30 or 31)). I've been trying to work with the  IF,AND,OR formula to use in CF where every 3rd month (Mar, Jun, Sep, & Dec) and the date (in this case, the 8th) of cell G5 occurs it will highlight G5. Am I even on the right track with the IF,AND,OR function? I've been working on this (off & on) for 5 days now and am getting nothing but headaches from it. Any & all advice would be GREATLY APPRECIATED. Thank you!!

[#2 · Accepted solution]

@dlcartin If your conditional formatting (CF) formula is to compare dates, they (probably) can't all be based on the result of the TODAY function; one of them should be a fixed (stored) date.  Does G5 contain a fixed future date that you want to highlight when it is only 3 or 6 or 9 months away?

In any case, the EDATE function is your friend.  So your CF formula for that cell might be:

=OR( G5=EDATE(TODAY(),3), G5=EDATE(TODAY(),6), G5=EDATE(TODAY(),9) )

Or if you still want to reference a cell containing "today's date" (that you can readily change the contents of, for testing the formula), replace TODAY() in that formula with $C$1.

You will notice that EDATE calculates 3 months after 31 Mar as 30 Jun (not 01 Jul), and 3 months after 30 Nov 2023 as 29 Feb 2024 (not 01 Mar 2024).  If you need alternative results … yeah, then your cell and/or CF formula(s) will be a lot more complicated, with special logic for end-of-month in January (3-month), March (3-month, 6-month), May (6-month, 9-month), …

Side note: If C1 contains =TODAY(), its value is (approximately) 44994, regardless of whether you have formatted it to display just the month number. Similarly for C2. To get those intended values, you would use =MONTH(TODAY()) and =DAY(TODAY()), respectively.

[#3]

Thank you for responding. To answer your question(s), yes, G5 is a constant date (in this case the 8th of every 3rd month beginning with Mar. And, C1 & C2 are formatted that way for visual simplicity. I tried your formula and it didn't work (gave me 'FALSE' condition when C1 is clearly TRUE (I did change G5 to equal C2 for testing purposes)). Perhaps I haven't made my goal clear, I wish for G5 (8th) to be highlighted in Mar, Jun, Sep, & Dec, when those month(s) & date occur I need a 'TRUE' value, else a 'FALSE' value. I admit that I don't understand your use of 'EDATE' (I try to stick with the 'IF' function (as I barely understand it's use)). But, again, I thank you for your response, you are most kind. Have a Blessed Day.

[#4]

UPDATE:
I believe that I have the formulae that I need (it seems to work, so far).
=AND(OR(C1=3,C1=6,C1=9,C1=12),C2=G5)
(I changed the value of G5 (for testing purposes) and it works).
Thanks again for the inspiration!