Excel Date format changes

R

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?
 
P

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top