Cells() produces reversed date format for double digit dates

**tarns** · 04-05-2006, 01:52 AM

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

Even when debugging and hardcoding the value to a cell i find this problem:
eg

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

=11/06/2004 9:39:19 AM

The format of the column i am retrieving all the info and saving to is of the format "Date: *14/03/2001" .

How can i change my code/imported text file so that the dates are read correctly?

Please Login or Register  to view this content.

edit: Read this on a website
Here's the unofficial (i.e. my) explanation. If your VBA code reads a date on a worksheet, it interprets the date in the same way as the worksheet does. But if VBA reads a date inside the code it assumes that it is written in month/day/year (i.e. US) date format. UNLESS (and there has to be a big UNLESS!) the date it reads is impossible. If it gets a date that doesn't conform to month/day/year it tries day/month/year, and if that doesn't work it tries year/month/day. If either of these work they get used.

Dave Peterson · 04-05-2006, 09:20 AM

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 wrote:
>
> 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" .
>
> --
> tarns
> ------------------------------------------------------------------------
> tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
> View this thread: http://www.excelforum.com/showthread...hreadid=529905

--

Dave Peterson

**tarns** · 04-05-2006, 08:49 PM

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

Please Login or Register  to view this content.

**tarns** · 04-05-2006, 08:56 PM

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 · 04-05-2006, 09:10 PM

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.

tarns wrote:
>
> 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
> ------------------------------------------------------------------------
> tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
> View this thread: http://www.excelforum.com/showthread...hreadid=529905

--

Dave Peterson

Dave Peterson · 04-05-2006, 09:10 PM

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.

tarns wrote:
>
> 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
>
> --
> tarns
> ------------------------------------------------------------------------
> tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
> View this thread: http://www.excelforum.com/showthread...hreadid=529905

--

Dave Peterson