How to convert a text field to a date
Hello,
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,
Sean.
Answers

You'll probably run into a similar issue when the month is 2 digits. Try this instead...
=DATE(VALUE("20" + RIGHT(RD@row, 2)), VALUE(MID(RD@row, FIND("/", RD@row) + 1, FIND("/", RD@row, 4)  (FIND("/", RD@row) + 1))), VALUE(LEFT(RD@row, FIND("/", RD@row)  1)))

Hi Paul,
This fixed the issue when the day is a single digit but now I get an error when the day has two digits. I'm going to see if I can fix it myself but assuming I can't can you look to see how to make it work both ways?
Thanks,
Sean.

It shouldn't be erroring out, but I misread your date format (I was working with UK format just before commenting). Assuming your date format is
mm/dd/yyyy
This should work (slight tweak after rearranging to provide more flexibility):
=DATE(VALUE("20" + RIGHT(RD@row, 2)), VALUE(LEFT(RD@row, FIND("/", RD@row)  1)), VALUE(MID(RD@row, FIND("/", RD@row) + 1, FIND("/", RD@row, FIND("/", RD@row) + 1)  (FIND("/", RD@row) + 1))))
Here's the breakdown...
Year:
VALUE("20" + RIGHT(RD@row, 2))
Grab the right two digits, put a "20" on the beginning and convert it to a number.
Month:
VALUE(LEFT(RD@row, FIND("/", RD@row)  1))
Left function to pull the leftmost digit(s) with the FIND function outputting the number of digits to pull. The 1 should push it back to one digit before the first /. Then a VALUE to convert it into a number.
Day:
VALUE(MID(RD@row, FIND("/", RD@row) + 1, FIND("/", RD@row, FIND("/", RD@row) + 1)  (FIND("/", RD@row) + 1)))
MID function to grab from the middle of the string. FIND + 1 to set the starting point. Then we find the second / and subtract the starting point from that to get the number of characters to pull. Wrapped in a VALUE to convert to a number.

That did it, works pefectly now regardless of how many digits exist in the month and day fields. Thanks Paul!


I did this but it made all my dates 1923. From a 08/13/23 text field. Any ideas?


=DATE(VALUE(RIGHT(Value@row, 2)), VALUE(MID(Value@row, FIND("/", Value@row)  2, 2)), VALUE(MID(Value@row, FIND("/", Value@row) + 1, 2)))
The value field is text: 08/10/23 and it returns as 08/10/23 but when I hover over the cell it shows as 1923

Help Article Resources
Categories
Check out the Formula Handbook template!