Excel Date format changes

[rji939]

I get CSV files that I convert to .XSLX files. My problem lies with the
timestamps. They show as MM/DD/YYYY H:MM:SS ie: 08/03/2010 08:12:00. The
problem is that this translates to August 3rd, 2010.... where it should
actually be March 8th, 2010. I've tried going to Data-->Text to column-->
and selecting DMY, but this does nothing.
I've used formula: =DATE(MID(D1,7,4),
MID(D1,4,2),LEFT(D1,2))+TIMEVALUE(MID(D1,12,5)) --- but I get VALUE error.

Any ideas?

 

[Paul C]

The formula does not work because the data is numeric and not a text string

First convert to a text string in a format you can work with
(Data in A1) formula for B1
=TEXT(A1,"mm/dd/yyyy")

Formula for C1
Then do the transpose into a date
=DATE(RIGHT(B1,4),MID(B1,4,2),LEFT(B1,2))