Simple Flag Formula

Lisa_Doris

I would like the formula for a simple formula for my flag column.

If the "status" is "in progress" and the "due date" is 2 days prior and also after today

Thanks in advance

L_123

=if(and(status@row= "in progress" ,[due date]@row > today()),1,0)

The above will show when status is in progress and due date is after today. I don't know what you mean by 2 days prior however, 2 days prior to what?

Lisa_Doris

Thanks, I thought that formula would work, but it doesn't. Is it an AND function?

I am new to making formulas and will keep trying different scenarios. Any help is appreciated

Andrée Starå

Hi @Lisa_Doris,

Can you paste the exact formula you’re trying to use?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Lisa_Doris

=IF(AND(Status@row = "in progress", AND([Start Date]@row > TODAY()), 1, 0))

I put it in the at risk (flag) column and I get incorrect argument message

Lisa_Doris

i also tried this: =IF(AND(Status@row = "in progress", ([Start Date]@row > TODAY()), 1, 0))

With the same error message

Lisa_Doris

I am getting tired! I realized in my examples my status and date was outside the parameters. I adjusted that and still no luck.

Sean Morgan

Hey @Lisa_Doris ,

You may want to try something like this:

=IF(AND(Status@row = "In Progress", [Due Date]@row = TODAY(-2)), 1, 0). This creates a flag if the Due Date is 2 days prior to today. Please see the results in my below screenshot:

The number in the TODAY(-2) can be modified to also say TODAY(2) which would flag the column if the Due Date was 2 days ahead of today.

Kindest Regards

Sean

Lisa_Doris

@Sean Morgan thank you! This works when it is exactly 2 days prior. How would I create a flag if the Start date is 2 days prior and/or past today and I need to add more status criteria- Not Started, Assigned, Scheduled, In Progress, a Flag is created.

To Recap if the Start date is 2 days prior or past today and the status is Not Started, Assigned, Scheduled, In Progress, a Flag is created.

Andrée Starå

@Lisa_Doris

Try something like this. I've added so it's not flagged if the date is empty.

=IF([Due Date]@row = "", "", IF(AND(Status@row = "In Progress", [Due Date]@row <= TODAY(-2)), 1, 0))

Did it work?

Lisa_Doris

Andrée Starå Thank you,

I was able to get the formula to work. But I need to add more criteria and I thought it would be easy to figure out, but I can't. How would you suggest I add more criteria, My status column is a drop down and I would like the formula to create a flag if one of several choices are selected generates the flag- Not Started, Assigned, Scheduled, In Progress

Thanks in advance

Andrée Starå

@Lisa_Doris

Happy to help!

What are the status options where it shouldn't get flagged?

Lisa_Doris

So Glad for your help!!

Complete, Cancelled, On Hold are the status criteria it would not be flagged.

If Not Started, Assigned, Scheduled, In Progress are with in 2 days of the start date or past the start date, then I want it to be flagged

Andrée Starå

@Lisa_Doris

I'm always happy to help!

Try something like this.

=IF([Due Date]@row = "", "", IF(AND(OR(Status@row = "Not Started", Status@row = "Assigned", Status@row = "Scheduled", Status@row = "In Progress"), [Due Date]@row <= TODAY(-2)), 1, 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?