Formula Help

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

Find more posts tagged with

Formulas

This should work:

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

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

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.

Gotcha. Makes sense. Ill try it. Thanks!

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

Happy to help.

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…