Formula Help

dstej

Hi,

I have a due date column and a status column. The different options for status's in the status column are -

In Progress

At-Risk

Completed

Postponed

What I would like is a formula that can do the following -

IF due date is empty or prior to date listed - mark status column to In Progress

IF due date is within 2 days - mark status column to At-Risk

Please help!

Thanks!

Kelly Moore

Hey @dstej

I believe this is the formula you asked for.

=IF(OR(AND([Due Date]@row <= TODAY(2), [Due Date]@row >= TODAY()), TODAY() > [Due Date]@row), "At-Risk", IF(OR([Due Date]@row = "", [Due Date]@row > TODAY()), "In Progress"))

I'm not sure that your formula is complete. I noticed that you did not have a condition for 'Completed' or 'Postponed'. If you let me know the rest of your conditions I can help you finish your formula. As written, all of your rows eventually will eventually be at risk as there is nothing 'Completed' to stop the clock from continuing to count down.

cheers

Kelly

dstej

Thank you so much! You are amazing!

Let me try what you have here.

We are making the individuals responsible for the task come into the sheet and update it as completed or postponed manually because there wouldn't be auto-trigger for those scenarios.

Somehow I think this is really close but just not 100%.

IF due date is empty or today's date is prior to due date - show In progress

IF due date is 2 days prior to today's date - show at risk

Thanks again!

Kelly Moore

Hey

I was concerned you might be manually entering Completed or Postponed. You won’t be able to use a single column for both manual entry and formula decisions. If needed, we can accommodate that with a helper column where they would enter manually and we would pull their response into this column via formula. Let me know and I’d be happy to help

dstej

Oh dear! Definitely did not know that. Yes I would be interested in setting up that helper column. That is a great idea!

thank you!

Kelly Moore

Hey @dstej

See if this works better

= IF([Due Date]@row = "", "In Progress", IF([Due Date]@row <= TODAY(2), "At-Risk", IF([Due Date]@row > TODAY(), "In Progress")))

The simplest helper column is another dropdown column with the choices of "Completed" and "Postponed". Consider this column as an override column for the formula column. For this reason, you might consider also adding the choice of "At-Risk". For this very reason I would definitely not include 'In Progress" as an option. Depending on how adaptable you users are, you can either re-name the formula column above and name the helper column to Status so your users can continue as usual, or call the helper something else. (If you want the helper to be called Status you will first have to rename the current Status column to something different). For the sake of clarity, I'll refer to the helper as Manual Entry. You can change this in the formula to suit you. (If you first call the helper column Manual Entry, then past this formula below into your current Status column, as you rename columns to suit your wishes the formula will update the new names automatically).

=IF([Manual Entry]@row<>"", [Manual Entry]@row, IF([Due Date]@row = "", "In Progress", IF([Due Date]@row <= TODAY(2), "At-Risk", IF([Due Date]@row > TODAY(), "In Progress"))))

If you have any trouble with the formula, shout out to me and we'll work til we get it right.

Kelly

dstej

oh my gosh! Thank you!

This one does work 100% = IF([Due Date]@row = "", "In Progress", IF([Due Date]@row <= TODAY(2), "At-Risk", IF([Due Date]@row > TODAY(), "In Progress")))

I have added the helper column and changed the names accordingly -

Status - change to - Working Status

Helper Column - change to - Final Status

So this next formula would be replacing the first formula in the Working Status column - correct?

= IF([Final Status]@row <> "", [Final Status]@row, IF([Due Date]@row = "", "In Progress", IF([Due Date]@row <= TODAY(2), "At-Risk", IF([Due Date]@row > TODAY(), "In Progress"))))

I believe everything is working!

I really appreciate this.

Thanks!

Denise 😃

Kelly Moore

Hey

Yes, this formula goes into your formerly called 'Status' column.

If you later decide on rules for Completed and/or Postponed, and you need help, let the community know. If you need help on your Health column - shout out.

cheers

Kelly