I got a strange format that I need to apply a format to, so Excel interprets it as a date.
It looks like this:
1/30/2018 3:58:07 PM(UTC-5)
1/30/2018 11:33:06 AM(UTC-5)
All these dates are currently sitting in a text column in my sheet.
I am unsuccessful in converting this column to date using text to columns and apply a format, I've tried mm:dd:yyyy:hh:mm:ss AM/PM
Power Query is suitable for such transformations, if you consider such option.
Select the range -> Replace (UTC on space -> Replace ) on nothing -> Change Type Using locale -> Type DateTimeTimezone, locale English (US) -> return result to Excel sheet