Extract date from cell into helper column

Bwoods113

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

Paul Newcome

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

Paul Newcome

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

Bwoods113

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

Paul Newcome

Try removing that equal symbol from before the first IF.

Bwoods113

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

Brandon

Paul Newcome

Happy to help. 👍️