Convert output of LEFT function to date

Options

Hello,

Couldn't find the answer already posted. I have a column that is a dropdown list of sprints where the format is the start date, end date, and sprint number all in one string. I would like to pull the start date out to use in another column to calculate end date based on the number of sprints.

When I use the LEFT function to pull out the start date the result is text and not a date, so any formulas using this column don't work correctly. My formulas for the Sprint Start date is =LEFT([Sprint Names]@row, 10). Is there a way to convert the output to a date? I tired the VALUE function but that didn't work.

Thank you!


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    This will find your year

    =VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4))

    This will only work until 2099. I am using "/20" as the separator as there is no month 20.

    And you must use 4 digit years.


    So your formula to find your date from your string is

    =DATE(VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4)), VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", "")), VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", "")))


    Here is an image that shows my workings:

    I make each part separately in it's own column and set up rows to test all the combinations of m/dd m/d mm/d mm/dd

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/22/23
    Options

    Hi

    You can use the DATE function to rebuild your date from the component parts. The syntax is

    =DATE(year, month, day)

    Ie, this will create a Date for today

    =DATE(2023,11,22)

    You can put functions instead of numbers to do this dynamically, but you have to specify each part of the date, not just:

    =DATE(LEFT([Sprint Names]@row, 10))

    Because you have some dates with one character day numbers and some with two, LEFT alone will not work for you. You need to use a FIND function to split the date you have into day, month, and year, and then put that into the formula.

    This formula works for my mm/dd/yy date format.

    We'd need to adapt that to work for your format.


    Does that make sense?

  • KPH
    KPH ✭✭✭✭✭✭
    Options


    This will find your day

    =VALUE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2))

    It takes 2 characters of text starting 1 character after the /

    However, if you have single digit days it will include the / as the second character, so we can substitute that with nothing like this

    =VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", ""))

    This will find your month

    =VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", ""))

    It takes the first 2 characters from the left and if one is a / it replaces it with nothing.

    Combined

    We can put them into the DATE formula like this:

    =DATE(VALUE("2023"), VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", "")), VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", "")))

    You may have noticed I fixed the year as 2023. I still need to figure out how to extract that from your string!

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    This will find your year

    =VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4))

    This will only work until 2099. I am using "/20" as the separator as there is no month 20.

    And you must use 4 digit years.


    So your formula to find your date from your string is

    =DATE(VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4)), VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", "")), VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", "")))


    Here is an image that shows my workings:

    I make each part separately in it's own column and set up rows to test all the combinations of m/dd m/d mm/d mm/dd

  • Dylan G
    Dylan G ✭✭
    Options

    Holy cow KPH! That is awesome, and it worked perfectly. Thank you so much!😀

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Happy to help @Dylan G, thanks for the ❤️

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 12/21/23
    Options

    If you are using a created column, or column with the 00/00/0000 format

    Worth nothing: this only works if the date your converting is at the start of the cell, if you have "abc123 12/21/23", it won't work correctly.

    In the example below, I'm using the created@row column.

    =DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(MID(Created@row, 1, 2)), VALUE(MID(Created@row, 4, 2)))
    
    Sincerely,

    Jacob Stey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!