Convert output of LEFT function to date
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!
Best Answer
-
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
Answers
-
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?
-
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!
-
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
-
Holy cow KPH! That is awesome, and it worked perfectly. Thank you so much!😀
-
Happy to help @Dylan G, thanks for the ❤️
-
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)))
Sincerely,
Jacob Stey
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 359 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!