How to change status based on start/end dates/if completed was checked or not.

Caitlin Bowman

Hi there,

I've been reviewing past topics but haven't been able to get those formulas to work. I have a sheet where there are start/end dates, and a completed check box column. I'm having trouble figuring out how to create a formula where if start date is in the past and end date is in the future, having it automatically change the status to in progress, same for if the end date is past and the check box is not checked, having it automatically changing the status to delayed, etc. Can you please help me with this?

Thanks in advance!

Caitlin

Paul Newcome

If you are able to provide a detailed list of the different criteria for each status, we may be able to figure something out.

I assume you want

"Complete": Checkbox is checked

"In Progress": Today is between the Start and Finish Date

"Delayed": Today is past the Finish Date

What are the other statuses and their criteria such as what if the start date is in the future?

What are your three column names as they are in the sheet?

Caitlin Bowman

Hi Paul,

Thanks for your quick response! That is correct. Our other status columns include "Not Started" (if status is in the future). Also, "Canceled" "Awaiting feedback" "On hold" but I'm wondering if those will have to be manual processes since they aren't necessarily date-related. The three column names are "Complete" (with the checkbox), "Start Date", "End Date".

Thanks!

Caitlin

Paul Newcome

Here's the catch... You can only have a formula OR manual entry. Once you manually enter data into a cell to include selecting from a dropdown, it will overwrite/delete the formula.

To automate the Status, you will need to come up with some other option for those 3 choices of "Cancelled", "Awaiting Feedback", or "On Hold" (maybe a separate dropdown or checkboxes). Once you figure out how you want to handle that part, we can work that criteria in to the overall formula that would automate all statuses.

Andrée Starå

@Caitlin Bowman

Hi Caitlin,

The third-party service, Zapier, could be an excellent option for this scenario. Is that an option for you?

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post 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!