First, be aware that your column-A "dates" are really just text that looks like a date.
None of the suggestions made so far work for me either. I think we're battling region code differences regarding what format of text Excel will automatically convert to a date.
With the --A1 approach 28-09-2021 11:56 AM gives me a #VALUE error because in the USA region Excel expects the 28 to be the month and the 09 to be the day. Similarly (and perhaps worse because there is no error) 12-09-2021 is interpreted as 9-Dec rather than 12-Sep.
There may be a more elegant way, but the following, I think, is region-proof:
In b3 copied down:
Formula:
=DATE(MID(A3, 7, 4), MID(A3, 4,2), LEFT(A3, 2)) + RIGHT(A3, 8)
Don't forget to format col-B as a Date & Time type.
Note, for simplicity, this formula assumes leading zeros are always present (as your example does). That is: not
15-9-2021 1:36 PM, but always:
15-09-2021 01:36 PM
Hope this helps
Bookmarks