Record a Date in the future

J.H.W.

The record a date automation is great. Just wondering if its on your roadmap at all to allow the ability to set the date recorded to be X days/months/yrs in the future. Eg, if a product doesnt have an expiry date, I want the date 2 yrs from today to be automatically recorded in the Expiry Date cell.

Other work around suggestions welcome!

Bassam Khalil

Hi @J.H.W

Hope you are fine, you can add a helper column call it expiry date and use the following formula on it and convert it to a column formula:

= [date recorded] + Today(730)

J.H.W.

Thanks @Bassam.M Khalil

I had thought of something like that but hadn't experimented yet as my experience with column formulas is that it effectively locks the column. If an product is entry is submitted via a webform with a different date to what the formula would return, will it overrode the formula or not? I really need all the dates in 1 column.

My other thought was if the entry was a product with a 2 yr expiry it moves to another sheet that has the helper column to calculate the 2yr date then it moves into the main register but that's getting abit busy I think!! 😀

John Pudar

I'll second the need for this feature. Being able to use Record a Date with a date in the future or past would be very helpful.

marti_funke

Yes! I need to set the automation to set a due date 14 days in the future from the date it was created. Is there no way to do this?

TerrieG633

I need to create an automation that triggers a date prior to a start date, so it should read something like this:

If {{onboard name}} starts on {{start date}} and {{start date is = to or < than 21 days from {{current date}} than send an email to {{ticketing system address}}.

See we can get a notification to onboard staff from 6 months to a year out, but creating tickets that far out clutters up our processes. But if I can automate it so that the notifications don't go out until 2-3 weeks prior, we can eliminate creating tickets for those who drop out or change dates. I'd also like to automate {{current date}} to today in some way.

Any Ideas?

Genevieve P.

Hi @TerrieG633

The way I would do this is with a helper column in the sheet with a formula. You can set a formula to look for your date conditions, then check a box when it's true - we can then set the trigger for the workflow to be based off of that checkbox.

For example:

=IF(AND([Start Date]@row > TODAY(), [Start Date]@row <= TODAY(21)), 1, 0)

Keep in mind that the TODAY function will only update to Today's date if the sheet is opened/refreshsed/saved somehow. See: IF Function / TODAY Function

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

TerrieG633

Thank you Genevieve P.! I'll take a look and see if I can make this work. I appreciate the assist!

TerrieG633

Ami Veltrie

@Genevieve P.

Hi Genevieve. Can you kindly confirm that your suggestion will actually record a future date TODAY? I am not following how it can do that.

I am only able to figure out how to record a future date on that FUTURE DATE.

I'm trying to record a date in a 'follow up date' field TODAY, that is 45 days from today.

Genevieve P.

Hi @Ami Veltrie

In your instance, you can use a workflow to record today's date in a hidden column when a row meets your criteria to follow-up. Then use a helper date column with a formula that adds 45 days onto that date:

=[Recorded Date] + 45

Ami Veltrie

@Genevieve P.

Thank you for that additional insight. The problem is that I need the future date to appear in the Follow Up Date column. Also, the Follow Up Date column cannot be a formula column because it must be editable by users as well.

Please let me know if I misunderstood. Thanks so much!

Genevieve P.

Hi @Ami Veltrie

There currently isn't a way to use a workflow to add a future date - the current workflow records Today's date. You are also correct that a column formula would restrict users from adding their own personal dates.

When you have a minute, please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community.

Ami Veltrie

Hi @Genevieve P.

Thank you for taking the time to confirm!

Will do.

Todd_Sharpe

The problem with the formulas is that they will change with any calculation. What we need is the ability to record a date in a field based on a calculation at the time and record it. Like setting a default. It also could be edited unless locked.

For example, I want to assign a due date 5 days in the future each time I create a task as a default. It want to be able to edit it later. I also don't want it changing to "today" using the "record a date" automation each time I edit say the start date or it changing as a calculation as it would always be 5 days from the start date.