Extracting Date from Drop Down Column to a Date Column

Shayma Gross
Shayma Gross ✭✭✭
edited 12/09/19 in Smartsheet Basics

I have a drop down column with the dates formatted as "Saturday, November 23, 2019" so people can select class dates.  I need to convert those to an actual date column so I can sort by date in a report but I cannot figure out the proper formula.  Any help would be great.  Thanks.

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    First thing to do is parse the data out.

    .

    To get the year:

     

    =VALUE(RIGHT([Dropdown Column]@row, 4)

    .

    Month would be:

     

    =MID([Dropdown Column]@row, FIND(" ", [Dropdown Column]@row) + 1, FIND(" ", [Dropdown Column]@row, FIND(" ", [Dropdown Column]@row) + 1) - FIND(" ", [Dropdown Column]@row))

    .

    Day would be:

     

    =VALUE(LEFT(RIGHT([Dropdown Column]@row, 8), 2)))

    .

    Then we would convert the Month text to a number. There are a few ways to do this. The most straightforward being a nested IF statement along the lines of:

     

    =IF(Month@row = "January", 1, IF(Month@row = "February", 2, IF(Month@row = "March", 3, .......................................................))))))))))))

     

    and continuing the pattern for all twelve months.

    .

    Then you would enter something like this into the new date type column.

     

    =DATE(Year@row, [Month Number]@row, Day@row)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks.  I just made a look up table and ran it that way.