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,
Help Article Resources
Check out the Formula Handbook template!