Restrict date choices?

Mary Payton
edited 01/05/22 in Smartsheet Basics

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!

Answers

  • Bassam Khalil
    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 .


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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
    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?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.