How to automate Status column?

System

This discussion was created from comments split from: IF todays date is between start date and end date then check this box.

Cam G38

Hi,

I have a similar request: i would like to automate the update of the 'Status' column to 'In progress' if the date is = or between my start date and my end date. I can't figure out how to make this work???

regards,

Paul Newcome

@Cam G38 What are the other statuses, and would they be automated as well?

Cam G38

'Confirmed' is the default status.

'Closed' is the status when the PTO is in the past (automation rule in place based on the finish date and working properly).

The only status I struggle with is 'In progress'

kowal

Hi @Cam G38,

one of the option is to create a Helping column (checkbox) which is also formula(and hide it later).

You can use this formula inside: =IF(AND(Start@row <= TODAY(), Finish@row >= TODAY()), 1, 0)

Once you test it and the checkbox will be changing itself if today is between start and end date you will create workflow (Change Cell Value) that will change the Status to In Progress if the checkbox is checked in Helping Column.

Hope it helps.

Paul Newcome

I would use a formula in place of automations:

=IF(Start@row > TODAY, "Confirmed", IF(Finish@row < TODAY(), "Closed", "In Progress")

If you wanted to use automations, you would set your trigger to run daily and then use conditions of the start being in the past and the finish being in the future. Either way, you should be able to accomplish this wiht your existing layout.