0

I need a formula that will do the following:

If cells in columns L, O or Q have a date of today or earlier then cell B must show the text "CHECK".

2 Answers 2

0

Put this in B2, and copy it to where you need it:

=IF(OR(L2<=DATEVALUE(NOW()),O2<=DATEVALUE(NOW()),Q2<=DATEVALUE(NOW())),"CHECK","-")
7
  • Thank you so much that worked perfectly, I've just realised I also need to factor this in too if possible: if cell f < or = 0
    – pdrops
    Jan 7, 2014 at 3:16
  • Maybe a spreadsheet tutorial would help, there are lots on the web. Also, please mark answers as correct. Jan 7, 2014 at 6:13
  • 1
    I tried =if(or(f2<=(0),L2<=DATEVALUE(now()),O2<=DATEVALUE(now()),Q2<=DATEVALUE(now())),"CHECK","-"), that seems to work - thanks again for your help
    – pdrops
    Jan 7, 2014 at 8:34
  • Eddy, I checked your solution and found it to be not correct. Your solution shows in all cases the word CHECK. See the second sheet of the example file I prepared. Jan 7, 2014 at 21:15
  • @JacobJanTuinstra it depends, it would fail your example, but not mine. As the OP does not provide an example, it is not possible to tell. But the OP did not report a problem, so I am guessing the solution I gave is fine. (Nice use of the Today() function, forgot they added that.) Jan 7, 2014 at 22:37
0

Use the following solution to accomplish that.

Formula

=ARRAYFORMULA(              
   IF(                          // first IF statement
     IF(                        // second IF statement
       L:L<>"",L:L<=TODAY()
     ) +                        // boolean OR statement
     IF(                        // third IF statement
       O:O<>"",O:O<=TODAY()
     ) +                        // boolean OR statement 
     IF(                        // fourth IF statement
       Q:Q<>"",Q:Q<=TODAY()
   )=1, "CHECK","")
 )

Copy/paste
=ARRAYFORMULA(IF(IF(L:L<>"",L:L<=TODAY()) + IF(O:O<>"",O:O<=TODAY()) + IF(Q:Q<>"",Q:Q<=TODAY())=1,"CHECK",""))

Screenshot

enter image description here

Explained

The second, third and fourth IF statements return a 0 if the statement is TRUE and a 1 if the statement if FALSE. The +'s in between act as a boolean operator OR. The first IF statement will validate the outcome of the three IF statements. The ARRAYFORMULA makes it possible to select column ranges, as you requested.

Example

I've created an example file for you: IF statement with boolean OR

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.