So i've been banging my head against the wall on this and tried various solutions to no avail.
I work for a US based company, but am based in Australia. On one of the reports we run, I get plain text dates formatted m/d/yyyy hh:mm:ss, which no matter which method I try, I can't seem to get d/m/yyyy hh:mm. I'm not even fussed about the exact number of characters, dd vs d or hh:mm:ss vs hh:mm.
If I ditch the time component, I can convert the dates around using
=DATE(RIGHT(A2,4),LEFT(A2,SEARCH("/",A2)-1),TRIM(MID(LEFT(A2,SEARCH("/",A2,SEARCH("/",A2)+1)-1),SEARCH("/",A2)+1,2)))
, but I can't seem to work out how to retain the time.
Any help or guidance would be much appreciated. Some sample fields as below:
Original:
3/20/2023 9:20:59 AM
3/20/2023 10:36:21 AM
3/18/2023 6:44:06 PM
3/19/2023 11:46:52 AM
3/17/2023 9:25:03 AM
Desired format (or something resembling this, I'm not precious as long as days come before months):
20/03/2023 9:20 AM
20/3/2023 9:20:59 AM is totally fine if easier.
Bookmarks