Formula Help

steve50951

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

 

Nic Larsen

This should work: 

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

steve50951

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

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

Gotcha. Makes sense. Ill try it. Thanks!

steve50951

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

Happy to help.