1

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".

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","-")
| improve this answer | |
  • 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 '14 at 3:16
  • Maybe a spreadsheet tutorial would help, there are lots on the web. Also, please mark answers as correct. – eddyparkinson Jan 7 '14 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 '14 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. – Jacob Jan Tuinstra Jan 7 '14 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.) – eddyparkinson Jan 7 '14 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

| improve this answer | |

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.