TEXT to DATE Formula
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.
Help Article Resources
Check out the Formula Handbook template!