Converting a drop down single select list of dates into a date I can use with Calendar App

SAM SALSMAN

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?

Mark Cronk

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

Mark Cronk

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

SAM SALSMAN

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.

Mark Cronk

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.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Mark

Paul Newcome

@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.

Javid

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

Paul Newcome

@Javid Try this...

=[Last Submitted]@row + IF(Recurrence@row = "Daily", 1, IF(Recurrence@row = "Weekly", 7, 30))