How to Lock Dates?

mindyphambui

Hi team, I would like to ask if it is possible to manually "lock dates" when building out a form. For example, if I have question #1 where I would like to lock the selected date, as of today's date or further. In other words, I would not like for the user to be able to select a past date. Additionally, is there a way to logically format the a follow up question (#2) with a constraint that the date must be 5 days out from the initially selected answer from question #1? Appreciate your help and thank you!

Jerry in SC

Mindy - Not sure if this will help you, but I might suggest you use "created by" as the date column for the day the form is submitted. Have the form user select priority (low, med, high). Then create a formula in the date column where if priority is high, add 5 days to the created by date. If medium, 7 days, and if low, 10 days.

mindyphambui

Hi Jerry, I have just tried to do so; however, I must have done something incorrectly as after applying the formula to the entire column, it is no longer showing up in my form.

For reference, the column "created by" only provided me with an email rather than a date, so I instead tried to use the column "created on" to provide me with a date. If say, my column A consisted of dates for when it was "created on" and column B were to be the targeted date of +5 from the initial "created on" date, then the formula worked perfectly in the sheet. However, when opening the form, it disappeared as if it was deleted from the form once the formula was put in place.

Would you happen to know how I can fix/better modify this to allow for the functionality to work via the form as well? Thank you!

Genevieve P.

Hi @mindyphambui

Smartsheet Forms currently cannot use formulas directly or limit date options in a date field.

What I would do in this instance is set up a Created Date column (as you've done) and use this as a reference for if the date submitted (in a different column) was less than that date (or in the Past),

If it is, then you can raise a flag or check a box in another column, like so:

=IF([Date Selected]@row < [Created Date]@row, 1, 0)

Then you could set up an Update Request workflow to send to the Form submitter if they've chosen an invalid date (if the flag is raised). See: Automatically request updates on tasks

Cheers,

Genevieve