Change Status and Formatting Based on Date Automation

[codaparty]

I’m looking to change a Status column which is currently a select list based on a “Start Date” and an “End Date” column which is date based.

Status column has selections like “Upcoming” “In Progress” etc.

I’d like a formula to change the status ideally 6 days before the start date (upcoming) and on the start date “In Progress” as well as to change formatting of the cell to a green/yellow etc. Worst comes to worse change the formatting of the whole row.

I’d also like to set up automations for email notifications based on those changes. I only see examples for email notifications, but not for internal automated changes. Thank you!

[Quentin_Morel]

Hello @codaparty,

here is help to your wish.

@codaparty

I’d like a formula to change the status ideally 6 days before the start date (upcoming) and on the start date “In Progress” as well as to change formatting of the cell to a green/yellow etc. Worst comes to worse change the formatting of the whole row.

I suggest this formula, that will compare the number of days before the start date (Start In) to ::

• your 6 days (I set as paramaters to switch from “Pending” to “Upcoming”)
• A value of 0 that means the task as begun (in progress)

This uses the switchif function, and I deal with the “finished” status checking if Today() is greater than the end date

Then I just set conditional formating according to status column

---

This status will be handled directly in coda with this formula, regard the difference of Today and the start/end date.

For automation to send mail, I’ll suggest to indeed set a time-based automation each day. It must check if one of the row hasa “6” (or the value of your slider) in “Start In” Column, and then send an email with the task we are talking about .

Is that clear for you ?

Thanks

Q

[codaparty]

@Quentin_Morel Thank you - I’m new to coda and don’t know where you’re getting the switchdays from or how you brought up that parameters block.Thank you

[Quentin_Morel]

Hi @codaparty

switchdays is the slider that has the value of “6” you except that you may wish in the future set 5 or 7 days > so it’s better for this value to be a paramaters rather than a frozen value. So this is why I’ve created the slider you can see.

If better please find the doc i’ve very quickly created for you :

Please let me know if this helps.

Q.

[codaparty]

Wow, thank you. I didn’t know you could embed these samples or use these sliders etc. So I got it functional in my doc, but I want all the cells that have no date not to read “completed”, ideally to be empty or read inactive, but I don’t know how to create an if statement for if the start date is empty.

[Quentin_Morel]

Hey @codaparty ,sorry for delay, you can use .IsBlank() function if([Start Date].IsBlank()=True(),X,Y) or just [Start Date]=""

I’ll let you try this