Formula Help

steve50951 · 02/14/19

I have an at risk column with flags. I am need a formula that will turn the flag red when its past a due date, and not marked complete.

I used this formula

=IF(AND([Date Needed By]1 < TODAY(), [Cabinet Complete]1 = 0), 1, 0)

this works for the current row, but when I drag down to copy to cells below, it automatically turns the flags red when there's no data in the cells.

something in the formula isn't copying correctly. Or maybe its not written exactly correct.

Thanks for you help.

SGF

Screen Shot 2019-02-14 at 11.02.19 AM.png

Screen Shot 2019-02-14 at 11.04.41 AM.png

Nic Larsen · 02/14/19

This should work:

=IF(AND([Date Needed By]@row < TODAY(), [Cabinet Complete]@row = 0), 1)

steve50951 · 02/15/19

Thanks for you help. The above formula, when copied, still left a red flag. If the cells in Date needed by were blank, it defaulted to a red flag.

Here's what I wound up using.

=IF(AND([Date Needed By]@row < TODAY(), ISDATE([Date Needed By]@row), [Cabinet Complete]@row = 0), 1)

do you think this will work?

SGF

Paul Newcome · 02/15/19

When a date cell is left blank, it is automatically assumed to be less than TODAY. To account for this, try using this...

=IF(ISDATE([Date Needed By]1), IF(AND([Date Needed By]1 < TODAY(), [Cabinet Complete]1 = 0), 1, 0))

All this does is says to run your existing formula only if there is an actual date in there.

steve50951 · 02/15/19

Gotcha. Makes sense. Ill try it. Thanks!

steve50951 · 02/15/19

So here's where I landed

=IF(ISDATE([Date Needed By]@row), IF(AND([Date Needed By]@row < TODAY(), [Cabinet Complete]@row = 0), 1, 0))

Seems to work.

thanks again for your help.

SGF

Paul Newcome · 02/15/19

Happy to help.

Comments