Cells() produces reversed date format for double digit dates

[tarns]

Hi There,

I have a text file that i import which has about 100 or so lines like
below.
"16","1/06/2004 9:39:19 AM","
"17","17/06/2004 12:29:14 PM",

When i use the "cells(row,col).value" function to insert the date, the
date is reversed for ALL dates that are not in the format dd/mm/yyyy.
Eg For every month of every year, the 1st through to the 9th days are
written backward since the format is d/mm/yyyy and i cannot modify this
text file.

Even when debugging and hardcoding the value to a cell i find this
problem:
eg
Code:
--------------------
Cells(3, 10).Value = "1/06/2004 9:39:19 AM"
-Cells(3, 10).Value = DateValue(Cells(3, 10).Value)-
--------------------

And the output is "6/01/2004" without any timestamp and date reversed
(with or without the DAtevalue conversion)

But this double digit date works

Code:
--------------------
Cells(3, 10).Value = "*11*/06/2004 9:39:19 AM"
--------------------
=11/06/2004 9:39:19 AM



Whats going on here?? (the format of the column i am retrieving all
the info and saving to is of the format "Date: *14/03/2001" .

 

[Dave Peterson]

If those dates/times are in one column, then I'd do a little more work after the
import macro runs. (You are importing the data via a macro, right?)

Insert an adjacent column to the right of the date/time column.

Then use data|text to columns for that column.
(It looks as though you may be able to use Fixed width (since the months are two
digits)).

Choose date (dmy) for the first part
choose General as the second.

======
If you have any control over the originating program, you may want to use two
digit days, months, hours, minutes, seconds. It could make life a bit easier.

 

[tarns]

Yes, the text to columns conversion is performed after the import but
already some of the dates are reversed and after i perform the text to
columns conversion im still left with incorrect data.

Column G contains all the dates/times


Code:
--------------------
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 9)), TrailingMinusNumbers:=True
Range("G3:G500").Select
Selection.NumberFormat = "dd/mm/yyyy"

 

[tarns]

I have no control over the program that produced it but im looking for
ways to manipulate the text file so it displays 2 digits for the day

 

[Dave Peterson]

Without knowing how your importing is done, I'd try to add the date procedure to
that import procedure.

Just because some of the data came in as dates doesn't mean that the dates are
correct.

 

[Dave Peterson]

If you really have to, you could use delimited by a space.

Then you'd end up with 3 columns--Date (choose the correct layout!), time
(without the AM/PM) and an AM/PM indicator.

You could use another column to adjust the date:

=c1 & if(d1="PM",.5,0)
drag down. And convert to values.
Delete the time and am/pm indicator column and format that helper column as a
nice time.