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.
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!