Extract date from cell into helper column
May 23 2023 02:53PM
This is how the date looks when it comes in, (it is pulled in from a 3rd party software via data shuttle). Trying to convert this into a helper column in this format: 5/23/2023 (removing the time stamp) so that I can us it in a report. Any help is greatly appreciated.
Thank you,
Brandon
Best Answer

You would need something like this:
=DATE(VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row) + 1) + 1, 4)), nested_if, VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row) + 1, 2)))
Where it says "nested_if" above, you will need to drop in a nested IF statement that looks at the month text and converts it into a number.
=IF(LEFT([Column Name]@row, 3) = "Jan", 1, IF(LEFT([Column Name]@row, 3) = "Feb", 2, IF(LEFT([Column name]@row, 3) = "Mar", 3, .....................................))))))))))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

You would need something like this:
=DATE(VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row) + 1) + 1, 4)), nested_if, VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row) + 1, 2)))
Where it says "nested_if" above, you will need to drop in a nested IF statement that looks at the month text and converts it into a number.
=IF(LEFT([Column Name]@row, 3) = "Jan", 1, IF(LEFT([Column Name]@row, 3) = "Feb", 2, IF(LEFT([Column name]@row, 3) = "Mar", 3, .....................................))))))))))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Thank you so much I understand the formula but I think I may have a sytax error, would you mind looking to see if you can find it? I cant seem to locate it.
=DATE(VALUE(MID([Last Started]@row, FIND(" ", [Last Started]@row, FIND(" ", [Last Started]@row) + 1) + 1, 4)), =IF(LEFT([Last Started]@row, 3) = "Jan", 1, IF(LEFT([Last Started]@row, 3) = "Feb", 2, IF(LEFT([Last Started]@row, 3) = "Mar", 3, IF(LEFT([Last Started]@row, 3) = "Apr", 4, IF(LEFT([Last Started]@row, 3) = "May", 5, IF(LEFT([Last Started]@row, 3) = "Jun", 6, IF(LEFT([Last Started]@row, 3) = "Jul", 7, IF(LEFT([Last Started]@row, 3) = "Aug", 8, IF(LEFT([Last Started]@row, 3) = "Sep", 9, IF(LEFT([Last Started]@row, 3) = "Oct", 10, IF(LEFT([Last Started]@row, 3) = "Nov", 11, IF(LEFT([Last Started]@row, 3) = "Dec", 12)))))))))))), VALUE(MID([Last Started]@row, FIND(" ", [Last Started]@row) + 1, 2)))

Try removing that equal symbol from before the first IF.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Thank you so much that was it, it works perfect. I appreciate your time and knowledge.
Brandon

Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 213 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!