Flagging a Date 30 days past due?

Guaca Mohle

I have a sheet with the following columns:

• Last Updated: date in which a customer profile was last edited
• Next Review Due: a date calculated as 4 months after the Last Updated Date -- this is the time at which an account team should review the customer profile data to ensure that it is still up-to-date and if any changes are made. A notification is sent out to these folk on this date reminding them of the needed follow up.

Now, I would like to flag any rows which have had a month elapse since that notification was sent out, and no updates recorded.

I'm thinking perhaps via conditional formatting change the Next Review Due date RED if today is 30days or more past, but I'm open to other options.

What formula do I need?

Paul Newcome

If you insert a flag type column, you could use a formula such as...

=IF([Next Review Due]@row < TODAY(-30), 1)

If the Next Review Due date is more than 30 days in the past, it will flag the column. Of course you can change the 1 to whatever you want for the output.

From there you can set up your conditional formatting or additional alerts based on this new column.

Guaca Mohle

That did the trick. Now, for a blank cell in Next Review Date column, I get a #INVALID OPERATION error. I've tried to use the IFERROR to just leave the cell blank, but not getting the syntax correct.

=IFERROR((IF([Next Review Due]@row < TODAY(-30), "No"),"")

Paul Newcome

You just have an extra opening parenthesis after the IFERROR.

=IFERROR(IF([Next Review Due]@row < TODAY(-30), "No"), "")

Paul Newcome

If you insert a flag type column, you could use a formula such as...

=IF([Next Review Due]@row < TODAY(-30), 1)

If the Next Review Due date is more than 30 days in the past, it will flag the column. Of course you can change the 1 to whatever you want for the output.

From there you can set up your conditional formatting or additional alerts based on this new column.

Guaca Mohle

That did the trick. Now, for a blank cell in Next Review Date column, I get a #INVALID OPERATION error. I've tried to use the IFERROR to just leave the cell blank, but not getting the syntax correct.

=IFERROR((IF([Next Review Due]@row < TODAY(-30), "No"),"")

Paul Newcome

You just have an extra opening parenthesis after the IFERROR.

=IFERROR(IF([Next Review Due]@row < TODAY(-30), "No"), "")

Guaca Mohle

AWESOME!!!!!!!!!!