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

Options

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

  • SAM SALSMAN
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Javid Try this...

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!