Restrict date choices?
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
-
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 .
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?
-
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!
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives