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

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.

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!