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