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!