How to convert a text field to a date

Sean Rebeck

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.

Paul Newcome

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

Sean Rebeck

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.

Paul Newcome

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.

Sean Rebeck

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

Paul Newcome

Happy to help. 👍️

Tyler Sassara

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

Paul Newcome

@Tyler Sassara Exactly what formula did you use?

Tyler Sassara

=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

Paul Newcome

You missed the

"20" +

Before the RIGHT function.