TEXT to DATE Formula

Options

Currently we are using an API to upload a "date" into our sheet, but can only upload it into a text column without causing errors with the API. To transform it into a date column, I have created another column that uses a formula to covert the text uploaded into a date format.

However, I'm having troubles simplifying the formula and make it work for text input as "1/1/2020", and "11/1/2020". My formula only seems to work with double digit months and double/single days, "10/10/2020", due to returning the character length.


Here is my formula currently:

=IF(ISBLANK(DATE@row), "", IF(LEN(DATE@row) = 10, DATE(VALUE(RIGHT(DATE@row, 4)), VALUE(LEFT(DATE@row, 2)), VALUE(MID(DATE@row, 4, 2))), DATE(VALUE(RIGHT(DATE@row, 4)), VALUE(LEFT(DATE@row, 2)), VALUE(MID(DATE@row, 4, 1)))))


This formula accounts for:

  • if the data is blank it will return blank
  • if the length of the data is 10 characters long it will grab the characters for the month, day, and year
  • if it is not 10 characters long it will return a single digit day.


What is not taken into consideration is single digit months.

Please let me know if there is a more efficient way to write this formula.

Tags:

Answers

  • Avery Rueckheim
    Options

    This is the formula I have currently ended up with and seems to be working so far:

    =IF(ISBLANK(DATE@row), "", IF(LEN(DATE@row) = 10, DATE(VALUE(RIGHT(DATE@row, 4)), VALUE(LEFT(DATE@row, 2)), VALUE(MID(DATE@row, 4, 2))), IF(AND(MID(DATE@row, 2, 1) = "/", LEN(DATE@row) = 9), DATE(VALUE(RIGHT(DATE@row, 4)), VALUE(LEFT(DATE@row, 1)), VALUE(MID(DATE@row, 3, 2))), IF(AND(MID(DATE@row, 3, 1) = "/", LEN(DATE@row) = 9), DATE(VALUE(RIGHT(DATE@row, 4)), VALUE(LEFT(DATE@row, 2)), VALUE(MID(DATE@row, 4, 1))), IF(LEN(DATE@row) = 8, DATE(VALUE(RIGHT(DATE@row, 4)), VALUE(LEFT(DATE@row, 1)), VALUE(MID(DATE@row, 3, 1))))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!