How to convert a text field to a date


I have a situation where I am pulling data from another app (Jira) The date format is not recognized by SS as a date, looks like this 3/8/23. I am using the following formula to conver that text to date that SS will recognize:

=DATE(VALUE(RIGHT(RD@row, 2)) + 100, VALUE(LEFT(RD@row, 1)), VALUE(MID(RD@row, 3, 2)))

It works perfectly as long as the day is 2 digits like 28 but it's only 1 digit it returns an error. Trying to figure out how to make this work for either case, 1 digit or 2 on the day. RD is the column with the text field I am pulling data from.

Thank you in advance,



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You'll probably run into a similar issue when the month is 2 digits. Try this instead...

    =DATE(VALUE("20" + RIGHT(RD@row, 2)), VALUE(MID(RD@row, FIND("/", RD@row) + 1, FIND("/", RD@row, 4) - (FIND("/", RD@row) + 1))), VALUE(LEFT(RD@row, FIND("/", RD@row) - 1)))

  • Hi Paul,

    This fixed the issue when the day is a single digit but now I get an error when the day has two digits. I'm going to see if I can fix it myself but assuming I can't can you look to see how to make it work both ways?



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It shouldn't be erroring out, but I misread your date format (I was working with UK format just before commenting). Assuming your date format is


    This should work (slight tweak after rearranging to provide more flexibility):

    =DATE(VALUE("20" + RIGHT(RD@row, 2)), VALUE(LEFT(RD@row, FIND("/", RD@row) - 1)), VALUE(MID(RD@row, FIND("/", RD@row) + 1, FIND("/", RD@row, FIND("/", RD@row) + 1) - (FIND("/", RD@row) + 1))))

    Here's the breakdown...


    VALUE("20" + RIGHT(RD@row, 2))

    Grab the right two digits, put a "20" on the beginning and convert it to a number.


    VALUE(LEFT(RD@row, FIND("/", RD@row) - 1))

    Left function to pull the leftmost digit(s) with the FIND function outputting the number of digits to pull. The -1 should push it back to one digit before the first /. Then a VALUE to convert it into a number.


    VALUE(MID(RD@row, FIND("/", RD@row) + 1, FIND("/", RD@row, FIND("/", RD@row) + 1) - (FIND("/", RD@row) + 1)))

    MID function to grab from the middle of the string. FIND + 1 to set the starting point. Then we find the second / and subtract the starting point from that to get the number of characters to pull. Wrapped in a VALUE to convert to a number.

  • That did it, works pefectly now regardless of how many digits exist in the month and day fields. Thanks Paul!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • I did this but it made all my dates 1923. From a 08/13/23 text field. Any ideas?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tyler Sassara Exactly what formula did you use?

  • Tyler Sassara
    Tyler Sassara ✭✭
    edited 08/18/23

    =DATE(VALUE(RIGHT(Value@row, 2)), VALUE(MID(Value@row, FIND("/", Value@row) - 2, 2)), VALUE(MID(Value@row, FIND("/", Value@row) + 1, 2)))

    The value field is text: 08/10/23 and it returns as 08/10/23 but when I hover over the cell it shows as 1923

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You missed the

    "20" +

    Before the RIGHT function.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!