Convert output of LEFT function to date

Dylan G

Hello,

Couldn't find the answer already posted. I have a column that is a dropdown list of sprints where the format is the start date, end date, and sprint number all in one string. I would like to pull the start date out to use in another column to calculate end date based on the number of sprints.

When I use the LEFT function to pull out the start date the result is text and not a date, so any formulas using this column don't work correctly. My formulas for the Sprint Start date is =LEFT([Sprint Names]@row, 10). Is there a way to convert the output to a date? I tired the VALUE function but that didn't work.

Thank you!

KPH

This will find your year

=VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4))

This will only work until 2099. I am using "/20" as the separator as there is no month 20.

And you must use 4 digit years.

So your formula to find your date from your string is

=DATE(VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4)), VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", "")), VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", "")))

Here is an image that shows my workings:

I make each part separately in it's own column and set up rows to test all the combinations of m/dd m/d mm/d mm/dd

KPH

Hi

You can use the DATE function to rebuild your date from the component parts. The syntax is

=DATE(year, month, day)

Ie, this will create a Date for today

=DATE(2023,11,22)

You can put functions instead of numbers to do this dynamically, but you have to specify each part of the date, not just:

=DATE(LEFT([Sprint Names]@row, 10))

Because you have some dates with one character day numbers and some with two, LEFT alone will not work for you. You need to use a FIND function to split the date you have into day, month, and year, and then put that into the formula.

This formula works for my mm/dd/yy date format.

We'd need to adapt that to work for your format.

Does that make sense?

KPH

This will find your day

=VALUE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2))

It takes 2 characters of text starting 1 character after the /

However, if you have single digit days it will include the / as the second character, so we can substitute that with nothing like this

=VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", ""))

This will find your month

=VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", ""))

It takes the first 2 characters from the left and if one is a / it replaces it with nothing.

Combined

We can put them into the DATE formula like this:

=DATE(VALUE("2023"), VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", "")), VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", "")))

You may have noticed I fixed the year as 2023. I still need to figure out how to extract that from your string!

KPH

This will find your year

=VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4))

This will only work until 2099. I am using "/20" as the separator as there is no month 20.

And you must use 4 digit years.

So your formula to find your date from your string is

=DATE(VALUE(MID([Sprint Names]@row, FIND("/20", [Sprint Names]@row) + 1, 4)), VALUE(SUBSTITUTE((LEFT([Sprint Names]@row, 2)), "/", "")), VALUE(SUBSTITUTE(MID([Sprint Names]@row, FIND("/", [Sprint Names]@row) + 1, 2), "/", "")))

Here is an image that shows my workings:

I make each part separately in it's own column and set up rows to test all the combinations of m/dd m/d mm/d mm/dd

Dylan G

Holy cow KPH! That is awesome, and it worked perfectly. Thank you so much!😀

KPH

Happy to help @Dylan G, thanks for the ❤️

SteyJ

If you are using a created column, or column with the 00/00/0000 format

Worth nothing: this only works if the date your converting is at the start of the cell, if you have "abc123 12/21/23", it won't work correctly.

In the example below, I'm using the created@row column.

=DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(MID(Created@row, 1, 2)), VALUE(MID(Created@row, 4, 2)))