Prev: Picture AlternativeText property Equivalent in Excel 97
Next: Dynamically Adding Textbox Control To Frame Object in VBA (Workshe
From: rji939 on 15 Mar 2010 13:29
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?
From: Paul C on 15 Mar 2010 13:50
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))
--
If this helps, please remember to click yes.


"rji939" wrote:

> 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?
 | 
Pages: 1
Prev: Picture AlternativeText property Equivalent in Excel 97
Next: Dynamically Adding Textbox Control To Frame Object in VBA (Workshe