Status Formula based on Date Range

Kaitlyn Carroll

Hello - I need help with a formula for a status column based on a date range (start and end columns).

Status options for the formula include: Not started, Live, Completed

For example, is the item has a date range from 2/21/23 to 2/28/23, the status should automatically change to "Live."

If the dates in the date range are in the past, it should be Completed; if in the future, Not started.

Any ideas?

Thanks!

Sing C

Hi @Kaitlyn Carroll,

The only time I have seen an #invalid operation error when using a formula on dates is to do with the column types. Check your Start and End columns are Date column types.

In terms of your second question. Is 'paused' the only other possible value? If so, you could:

• Add a checkbox column called "Paused?"
• Update the formula in the status column to evaluate that column as well as the Start and End columns, to set the value accordingly

Something like this:

=IF([Paused?]@row = 1, "Paused", IF(Start@row > TODAY(), "Not Started", IF(End@row < TODAY(), "Completed", "Live")))

If you look at the first two rows in this test sheet, you'll see the same dates but one is paused due to that being the first check in the formula, overriding the date comparisons in the rest of the formula:

I've also intentionally left the last two rows with no dates, where the status is evaluated as 'Completed" because that's the catch-all part at the end of the formula. You could enhance the formula again to check for blank dates and leave the status blank where some combination of the dates are blank.

This one shows a blank status where both date columns are blank. Making it the first check so you don't see "Paused" when the Paused? column is checked and there are no dates.

=IF(AND(ISBLANK(Start@row), ISBLANK(End@row)), "", IF([Paused?]@row = 1, "Paused", IF(Start@row > TODAY(), "Not Started", IF(End@row < TODAY(), "Completed", "Live"))))

Which gives this:

Hopefully that gets you headed in the right direction. Let me know how it works for you.

Have a wonderful day!

✅ If my comment addresses your query, please support the Community by marking it as an Accepted Answer. This helps others find the solutions like yours more quickly. I would also be grateful for your "Insightful" or "Vote Up".

Sing C

Hi @Kaitlyn Carroll,

I'm making some assumptions about the business logic (in terms of different combinations of start and end date relative to today's date) but try this column formula as a starting point to see if it does the trick for you:

=IF(Start@row > TODAY(), "Not Started", IF(End@row < TODAY(), "Completed", "Live"))

The rules here are fairly basic:

• If start is in the future, status = Not Started
• If end is in the past, status = Completed
• Anything else, status = Live (this, for example, would cover the scenario where the start date is in the past and the end date is in the future).

I hope that helps. Let me know how you get on!

✅ If my comment addresses your query, please support the Community by marking it as an Accepted Answer. This helps others find the solutions like yours more quickly. I would also be grateful for your "Insightful" or "Vote Up".

Kaitlyn Carroll

Hi @Sing C !

Thank you for the quick reply. So far, that formula seems to work. The only error I received was an #invalid operation for a date range where the start and end date are in the future. But there are many others that are the same and don't have the error message. So I'm not sure what the issue is.

Additional question: the status column with this formula, there are other status options like "paused" but that would need to be manually selected - it wouldn't be dependent on the date range. However, I noticed that when a convert to column formula, the column no longer lets you manually selected status. I'm assuming that can't be changed? It's either have a formula to automate the status, or don't and manually change the status?

Sing C

Hi @Kaitlyn Carroll,

The only time I have seen an #invalid operation error when using a formula on dates is to do with the column types. Check your Start and End columns are Date column types.

In terms of your second question. Is 'paused' the only other possible value? If so, you could:

• Add a checkbox column called "Paused?"
• Update the formula in the status column to evaluate that column as well as the Start and End columns, to set the value accordingly

Something like this:

=IF([Paused?]@row = 1, "Paused", IF(Start@row > TODAY(), "Not Started", IF(End@row < TODAY(), "Completed", "Live")))

If you look at the first two rows in this test sheet, you'll see the same dates but one is paused due to that being the first check in the formula, overriding the date comparisons in the rest of the formula:

I've also intentionally left the last two rows with no dates, where the status is evaluated as 'Completed" because that's the catch-all part at the end of the formula. You could enhance the formula again to check for blank dates and leave the status blank where some combination of the dates are blank.

This one shows a blank status where both date columns are blank. Making it the first check so you don't see "Paused" when the Paused? column is checked and there are no dates.

=IF(AND(ISBLANK(Start@row), ISBLANK(End@row)), "", IF([Paused?]@row = 1, "Paused", IF(Start@row > TODAY(), "Not Started", IF(End@row < TODAY(), "Completed", "Live"))))

Which gives this:

Hopefully that gets you headed in the right direction. Let me know how it works for you.

Have a wonderful day!

✅ If my comment addresses your query, please support the Community by marking it as an Accepted Answer. This helps others find the solutions like yours more quickly. I would also be grateful for your "Insightful" or "Vote Up".

Kaitlyn Carroll

You are a genius! This works perfectly. Thank you for your recommendation and building out this formula. I also appreciate the detailed explanation. 😃

I just realized the #invalidoperation is because one of the dates is not a real date (2/30/23).

Thank you again so much! This is wonderful!!

Sing C

You're most welcome @Kaitlyn Carroll.

Glad I could help!

Take care!