Converting a drop down single select list of dates into a date I can use with Calendar App
I am new to Smartsheets and trying to convert a single select list of dates into a format that can be used by the calendar app.
I need these dates ..
Tuesday, December 29, 2020
Wednesday, December 30, 2020
Thursday, December 31, 2020
Monday, January 4, 2021
to be used to enter events into the calendar app for requests to schedule appointments. I want the choices to be restricted to specific days. Am I going about this wrong way, is there another way to limit dates within the calendar app?
Best Answer
-
Hi @SAM SALSMAN ,
Calendar needs data in a date column. Drop downs are text. You can make it work with a formula but you have to change your drop down dates to a XX/XX/XXXX format. Create a new date column and insert this formula to convert your text to a date. Then use the date column in your calendar:
=DATE(VALUE(RIGHT([date text]@row, 4)), VALUE(LEFT([date text]@row, 2)), VALUE(MID([date text]@row, 4, 2)))
Substitute your column name for [date text].
If you want to have a day in your text date you can but you'll need to adjust the parsing in the formula. Easiest if each day is abbreviate to the same # of characters, e.g. WED, TUR, FRI.
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @SAM SALSMAN ,
Calendar needs data in a date column. Drop downs are text. You can make it work with a formula but you have to change your drop down dates to a XX/XX/XXXX format. Create a new date column and insert this formula to convert your text to a date. Then use the date column in your calendar:
=DATE(VALUE(RIGHT([date text]@row, 4)), VALUE(LEFT([date text]@row, 2)), VALUE(MID([date text]@row, 4, 2)))
Substitute your column name for [date text].
If you want to have a day in your text date you can but you'll need to adjust the parsing in the formula. Easiest if each day is abbreviate to the same # of characters, e.g. WED, TUR, FRI.
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Can I add a time element to this as well? We are taking appointments in 30 minute increments.
I can group them by the text time slots as a work around, but it doesn't translate when I publish to our google calendars.
-
Good evening @SAM SALSMAN ,
Time in Smartsheets is tricky. I end up with text work around too.
@Paul Newcome maintains the holy grail of all things time. The link is below. Hopefully it includes a solution for you.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk Thanks for the shout-out!
@SAM SALSMAN To incorporate a time stamp, there are a few different options. If you are wanting to group by date/time and have them sorted, I would suggest creating a helper column that would contain a formula that will populate a 12 digit number that correlates to the date/time in a "yyyymmddhhmm" type of format. Then you can sort on this column to have them all in chronological order.
To get the yyyymmdd portion, you would use something along the lines of:
=YEAR([Date Column]@row) + "" + IF(MONTH([Date Column]@row) < 10, "0") + MONTH([Date Column]@row) + IF(DAY([Date Column]@row) < 10, "0") + DAY([Date Column]@row)
Getting the hhmm portion will depend on a handful of variables based on the format of your times.
If that sounds like a solution you would be interested in, let me know, and I will provide some additional questions and see if we can build out a solution for you.
-
How do I calculate & convert dropdown text (Daily, Weekly, Monthly....) where Day = 1, Week = 7..... into number of days so it can be calculated into my sheet's 'Recurrence' filed (Re attached)
?
Thanks
-
@Javid Try this...
=[Last Submitted]@row + IF(Recurrence@row = "Daily", 1, IF(Recurrence@row = "Weekly", 7, 30))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!