Formula to look at Due Date and Status columns and return check if criteria matches

steinkj

Status column with drop down: Not Started, In Progress, Waiting on Data from elsewhere, Complete

Due Date = Date

Actual Date Completed = Date

Not Completed & Past Due - check box

I want to be able to pull the children rows (along with the Parent) to a report or Dashboard for quick review that are past the due date and that are not equal to complete.

So I am trying to come up with a formula for the Not Completed & Past Due - check box

=if(AND([Due Date]@row>Today(1);[Status]@row,"/","Complete");1;0) does not work

=if(AND(Today(1)>[Due Date]3>);(Not [Status]3="Complete",1)) does not work

=IF(AND(TODAY(1) > [Due Date]3, NOT(Status3 = "Completed")), 1) places check box for all drop down items

I am new to formulas and know that my today is totally wrong for past due...but just trying to get the box to check

Genevieve P.

Hi @steinkj

There are a couple of things to note here.

In some of your formula examples you're using both ; and , to separate the different instructions. Since your final formula gives you an output, it looks like the language settings you have will want a comma instead of a semi colon, so always try to use , to separate things in a formula.

The next detail to go over is that formulas will look for exact matches when you have a word put in quotes, such as "Complete" or "Completed". You will need to make sure that the word you're looking to exclude is spelled exactly the same as what's in your Status column.

Try this:

=IF(AND([Due Date]@row > TODAY(), Status@row <> "Complete"), 1, 0)

I've adjusted the word you're looking to skip to be "Complete" versus "Completed" with a d. The <> says not.

Let me know if this makes sense and does what you're looking to do! If not, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

Genevieve P.

Hi @steinkj

There are a couple of things to note here.

In some of your formula examples you're using both ; and , to separate the different instructions. Since your final formula gives you an output, it looks like the language settings you have will want a comma instead of a semi colon, so always try to use , to separate things in a formula.

The next detail to go over is that formulas will look for exact matches when you have a word put in quotes, such as "Complete" or "Completed". You will need to make sure that the word you're looking to exclude is spelled exactly the same as what's in your Status column.

Try this:

=IF(AND([Due Date]@row > TODAY(), Status@row <> "Complete"), 1, 0)

I've adjusted the word you're looking to skip to be "Complete" versus "Completed" with a d. The <> says not.

Let me know if this makes sense and does what you're looking to do! If not, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

steinkj

Thank you!!!