How to automatically add a "Date+2 days" formula to a cell when a new row is added?

Heidi Larson

In our request tracking database, currently if a Date Requested is "in the past" and another condition is not met, the entire row turns red to alert us of a new request. This is great, except that sometimes the request comes in after hours, and first thing the next morning the row is red. This gives our clients the impression that we're not on the ball, and we'd like to give ourselves 48 hours before the row changes color.

I've created a helper column "Request date + 2 work days" and added this formula, which works manually: =WORKDAY([Date of Request]@row, +2)

How can I add an automated workflow such that as soon as a user submits a new request via our request form (meaning, a new row is added into the sheet), this formula is added to the new empty cell in the "Request date + 2 work days" column?

I've tried the Change Cell Value workflow, but it doesn't include date columns in the Change Cell Value / Select a Column dropdown. The formula doesn't work if column is anything but a date property. The Record a Date option only inputs the current date. Seems like it should be easy. What am I missing? Thanks.

Heidi Larson

Or even better, how can I add the condition to the Conditional Formatting rule that waits 2 business days before turning the row red? None of the existing options seem right.

Andrée Starå

Hi @Heidi Larson

I hope you're well and safe!

Please have a look at my post below with a method I developed where you can store the time, and then you build on that to compare to the current time.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!