Restrict date choices?

Mary Payton

Is there a way to restrict users to a defined set of dates while still utilizing a date column type? I need users to select from specific dates that correspond to the start of pay cycles, but I need it to be a date (not text) so that I can perform a calculation with it. Alternatively, if I offered a dropdown menu of date choices (text) is there a formula to convert this column data to a date format so that I can perform a calculation?

Thank you!

Bassam Khalil

Hi @Mary Payton ,

hope you are fine, i hope the following will solve your problem:

1- use dropdown column type to input your defined set of dates ( use this format for defined date 04/09/2017 dd/mm/yyyy)

2- create 3 column type Text / Number ( Year , Month , Day )

3- in year column use this formula to read the year number ( =VALUE(RIGHT([defined set of dates]@row, 4))

4- in Month column use this formula to read the Month number ( =VALUE(MID([defined set of dates]@row, 4, 2))

5- in Day column use this formula to read the Day number (=VALUE(LEFT([defined set of dates]@row, 2))

6- create column type Date and name it Defined Date and use the following formula to convert the date selected from the dropdown ( defined set of dates ) to date format you can use it in your calculation ( =DATE(year@row, month@row, Day@row) ) then you will get the selected date in date format.

7-convert all formulas to column formula .

Mark Gable

All - looking to resurface this question as I do not believe that it was appropriately answered.

Requirement: Restrict the dates that can be selected in a Date column.

Example: Booking hotel stay dates. Once a specific date is fully booked, it is no longer available for selection.

Are there any parameters that can be used for this?

Workaround: IF you advise to define the column as a Dropdown - single select, listing only permitted dates in a drop down list. How can the removal of choices be automated?

Jeff Reisman

@Mark Gable

Dynamic dropdown lists are available in Smartsheet Control Center, an add-on module. There's no parameter to accomplish this in standard Smartsheet, even with an Enterprise-level plan.

Requirement: Restrict the dates that can be selected in a Date column.

Example: Booking hotel stay dates. Once a specific date is fully booked, it is no longer available for selection.

Are there any parameters that can be used for this?

Andrée Starå

Hi @Mark Gable

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

• You can use the Premium app Data Shuttle to "sync" dropdown lists.

Is that an option?

I hope that helps!

Have a fantastic week & Happy New Year!

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!