TEXT to DATE Formula

Avery Rueckheim

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.

Avery Rueckheim

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))))))))