Using IF/And statements

Alicia D

I am running into the issue where I made a formula to flag a row if the due date is set to today or before today. But I don't know how to change it to not flag if the job has already been completed. Here is my current formula: =IF([End Date]@row <= TODAY(0), 1, 0) and here is what it is producing (below). But I don't want the flag to be there once the job is complete.

DKazatsky2

Hi @Alicia D,

Try this.

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

Hope this helps,

Dave

Alicia D

@DKazatsky2 This worked, thank you! Is there a way to have it so that it doesn't fill in the areas with blank spots in the status column? See below

DKazatsky2

@Alicia D,

This is untested but see if this does it.

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

DKazatsky2

Hi @Alicia D,

Try this.

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

Hope this helps,

Dave

Alicia D

@DKazatsky2 This worked, thank you! Is there a way to have it so that it doesn't fill in the areas with blank spots in the status column? See below

DKazatsky2

@Alicia D,

This is untested but see if this does it.

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

Alicia D

This worked, thank you so much!