Thread: [Solved] Persistent problem with date formatting
View Single Post
 
Old 06-18-2021, 12:45 PM
Purfleet Purfleet is offline Windows 10 Office 2019
Expert
  
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default
Column A is text with non printable characters and spaces, then it is also in US format (maybe a setting in Excel or your bank).

I would add another column with the below formula

=MID(TRIM(CLEAN(A2)),4,2)&"/"&MID(TRIM(CLEAN(A2)),1,2)&"/"&MID(TRIM(CLEAN(A2)),7,4)

then in the next column add zero to make it a number

Probably can be done in Power Query but i have hardly ever used PQ
Attached Files
File Type: xlsx Abbreviated Excel spreadsheet_ar12.xlsx (17.5 KB, 8 views)
Reply With Quote