SOLUTION: Mixed up day/month order on data exports

[Fysio]

Hi all,

Took me a little while to figure this out, so I thought I'd share the solution.

Tangerine Bank exports transactions in CSV format, and the dates they include don't match the month/day order my computer is set for. This meant that none of the date information was correct; half auto-corrected to date format with mixed up months/days and the other half just remained a m/dd/yyyy format.

To resolve the issue, create a second column next to the erronious date column and paste this formula.
Adjust the references so they are all on the same line, then copy it all the way down.

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(2022,LEFT(A2,(FIND("/",A2,1)-1)), SUBSTITUTE(MID(SUBSTITUTE("/" & A2&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")))

Cheers =]

 

[Fluff]

Hi & welcome to MrExcel.
Another option would be to use the Text To Columns feature on the data tab & select delimited, Next, clear all checkboxes, next & then select the date format the csv is in, Finish

 

[Fysio]

Thanks Fluff,
That would work if Excel didn't force an auto-format. I had to resort to the formula above due to it doing that.

For example, this is first thing I see when opening the CSV export from Tangerine:

Date	Transaction	Name
12-10-2022​	FEE	Interac Network Usage Charge
12-11-2022​	OTHER	Withdrawal to Tangerine Chequing
12-12-2022​	OTHER	EFT Deposit from CANADA
12/13/2022	ATM	Withdrawal - Scotiabank ABM
12/13/2022	CREDIT	Internet Deposit from Tangerine
12/13/2022	CREDIT	Internet Deposit from Tangerine
12/13/2022	OTHER	INTERAC e-Transfer To: IslandQue

This is what it looks like formatted to "Long Date":

Date	Transaction	Name
October 12, 2022​	FEE	Interac Network Usage Charge
November 12, 2022​	OTHER	Withdrawal to Tangerine Chequing
December 12, 2022​	OTHER	EFT Deposit from CANADA
12/13/2022	ATM	Withdrawal - Scotiabank ABM
12/13/2022	CREDIT	Internet Deposit from Tangerine
12/13/2022	CREDIT	Internet Deposit from Tangerine

This shows that some of the numbers were seen as dates, though the month and day were swapped. These SHOULD be Dec 10 to Dec 13.

 

[Fluff]

Text to columns should still work as long as you select MDY as the date format.