Forum Discussion
IF,AND,OR
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.
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.
- 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! - 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.
