Bank download date issue (mm-dd-yyyy vs dd-mm-yyyy)

[muzikman69]

I am wonder if anyone else has run into issues when downloading mass banking transaction tables which are in the mm-dd-yyyy format.

My issue is that my Excel (2007) is recognizing these dates as being dd-mm-yyyy; hence errors after the 12th day of a month and the wrong month on top of that. I am unable to split the text of the cell up and do a mass re-order of the dd/mm/yyyy numbers as it has already been converted to the date serial number format. (40459 eg).


The issue is complicated because as soon as the transactions are downloaded from the bank excel is converting the field from text/number to the date serial number; which essentially means I do not have the raw text to manipulate.

Any ideas?



A couple examples:
Raw Data -- Excel Date -- Actual Date
08/03/2010 -- Mar 8, 2010 -- Aug 3, 2010
08/04/2010 -- Apr 8, 2010 -- Aug 4, 2010
08/04/2010 -- Apr 8, 2010 -- Aug 4, 2010
08/09/2010 -- Sep 8, 2010 -- Aug 9, 2010
08/09/2010 -- Sep 8, 2010 -- Aug 9, 2010
08/09/2010 -- Sep 8, 2010 -- Aug 9, 2010
08/09/2010 -- Sep 8, 2010 -- Aug 9, 2010
08/10/2010 -- Oct 8, 2010 -- Aug 10, 2010
08/11/2010 -- Nov 8, 2010 -- Aug 11, 2010
8/16/2010 -- Error -- Aug 16, 2010
8/20/2010 -- Error -- Aug 20, 2010
etc.....

 

[Ruddles]

If you format the receiving column as Text beforehand, does that stop the date being converted to an Excel date serial?

 

[muzikman69]

Yes that does work if I do as explained in next paragraph:

I just opened the (CSV) file as a text document, and then copied the text over to Excel, and did a delimited text to columns which worked fine.

If I open the CSV directly with Excel the conversion happens automatically, and does not allow for that column to be specified as text.


My goal is to not have to do the CSV-->Text-->Excel-->Text-to-columns every time I download banking transactions if possible.


Cheers,
Graeme

 

[pjstock]

Graeme,
I am having the same problem
did you figure out a solution?

Peter

 

[muzikman69]

Peter,

If I recall correctly the solution may have been as simple as closing Excel and starting fresh with opening a new instance. However I can't recall off the top of my head. I don't seem to run into that issue anymore, I suspect my bank changed their format back some time ago. (??)

The work-around is to open it as a TXT file and paste it in, and then do your own text-to-columns (which is a pain).


Sorry I can't help more,

Cheers,
Graeme

Graeme,
I am having the same problem
did you figure out a solution?

Peter

 

[Marcol]

There are other ways but if you put this in a spare column and Drag/Fill Down

=IF(A2="","",IF(ISTEXT(A2),DATE(MID(SUBSTITUTE(A2,"/",REPT(" ",255)),2*255,255),LEFT(SUBSTITUTE(A2,"/",REPT(" ",255)),255),MID(SUBSTITUTE(A2,"/",REPT(" ",255)),255,255)),DATE(YEAR(A2),DAY(A2),MONTH(A2))))

Then Copy and Paste Special > Values

Excel Workbook
	A	B	C	D
1	Raw Data	Excel Date	Actual Date	*
2	08/03/2010	Mar 8, 2010	Aug 3, 2010	Aug 3 2010
3	08/04/2010	Apr 8, 2010	Aug 4, 2010	Aug 4 2010
4	08/04/2010	Apr 8, 2010	Aug 4, 2010	Aug 4 2010
5	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug 9 2010
6	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug 9 2010
7	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug 9 2010
8	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug 9 2010
9	08/10/2010	Oct 8, 2010	Aug 10, 2010	Aug 10 2010
10	08/11/2010	Nov 8, 2010	Aug 11, 2010	Aug 11 2010
11	8/16/2010	Error	Aug 16, 2010	Aug 16 2010
12	8/20/2010	Error	Aug 20, 2010	Aug 20 2010
Sheet2

 

[Akinrotimi]

=DATE(RIGHT(TEXT(a2,"dd/mm/yyyy"),4),MID(TEXT(a2,"dd/mm/yyyy"),4,2),LEFT(TEXT(a2,"dd/mm/yyyy"),2)) solves the problem.

Rotimi

 

[Marcol]

@ Akinrotimi

... solves the problem.

Are you sure?

Excel Workbook
	A	B	C	D	E	F
1	Raw Data	Excel Date	Actual Date	One way		Akinrotimi
2	08/03/2010	Mar 8, 2010	Aug 3, 2010	Aug, 3 2010		Mar, 8 2010
3	08/04/2010	Apr 8, 2010	Aug 4, 2010	Aug, 4 2010		Apr, 8 2010
4	08/04/2010	Apr 8, 2010	Aug 4, 2010	Aug, 4 2010		Apr, 8 2010
5	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug, 9 2010		Sep, 8 2010
6	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug, 9 2010		Sep, 8 2010
7	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug, 9 2010		Sep, 8 2010
8	08/09/2010	Sep 8, 2010	Aug 9, 2010	Aug, 9 2010		Sep, 8 2010
9	08/10/2010	Oct 8, 2010	Aug 10, 2010	Aug, 10 2010		Oct, 8 2010
10	08/11/2010	Nov 8, 2010	Aug 11, 2010	Aug, 11 2010		Nov, 8 2010
11	8/16/2010	Error	Aug 16, 2010	Aug, 16 2010		#VALUE!
12	8/20/2010	Error	Aug 20, 2010	Aug, 20 2010		#VALUE!
Sheet1