Keep in mind that Excel isn't storing a date.
If it finds a string that it INTERPRETS as a date, then it calculates the number
of days between 1/1/1900 and this date and stores the number.
Excel can then DISPLAY the number as a date.
If the string isn't in a "proper" date format, then Excel interprets it as a
string.
Second.. Excel considers a "proper" date as one in the format set in the
Regional Settings,
or a series of pre-defined formats (like 05-Nov-2010)
but it would NOT consider 05-Nov/2010 a proper date.
I think the problem is that YOUR windows session settings are for mm/dd/yyyy.
So when Excel reads in the date 06/10/2010, since it is a valid data in
mm/dd/yyyy format,
then it accepts it without reversing the mm/dd and stores it as an excel "date"
number..
But when it reads 31/08/2010, it recognizes that it it NOT a valid date in
mm/dd/yyyy format,
so it stores it as a string that LOOKS like a date in dd/mm/yyyy format.
If YOUR machine is the only one that will be importing these files, then you
can
change your
regional settings:
Control Panel
Regional and Language Options
Customize
Date
Short date format: dd/mm/yyyy
Now, if the machines that CREATE these CSV files have different regional
settings,
then you're going to have to check for valid dates in the files before
importing
them.
To do THAT, I would open the csv file as a text stream, then read through each
line, looking
for a date string and check the format.
then, once the format is determined, close and reopen the file and swap the
mm/dd if necessary.
whatever method you choose.
You must FIRST decide which format your WINDOWS SYSTEM is using so that you can
put the mm/dd
or dd/mm in the proper order.
Once Excel properly identifies a string as a date, it will store it as a number.
Then you can DISPLAY it in any format you choose.
If the files will ALWAYS be in dd/mm/yyyy format, and you want your regional
settings to stay mm/dd/yyyy,
then you're going to have to open the files as a stream and to string
manipulation to get the dates
in the right format.
does that help?
do you need more help?
Paul
----- Original Message ----
> From: Andy <andyr...@hotmail.co.uk>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Fri, November 5, 2010 9:16:40 AM
> Subject: $$Excel-Macros$$ Date confusion when copying from CSV to Excel
>
> Hey there.
>
> This problem is giving me a major headache and no post I have read can
> seem to help with the problem I am having.
>
> I have some code to copy any present data from CSV files to a main
> workbook (basebook) but some seem to paste over as mm/dd/yy instead of
> dd/mm/yy...
>
> Two of the CSV's that import differently have the following formats
> when I open them through Excel:
> 06/10/2010 07:00:00 (custom - dd/mm/yyyy hh:mm) - copies to Excel as
> 10/06/2010 07:00:00
> 31/08/2010 13:00:00 (custom - dd/mm/yyyy hh:mm) - copies to Excel as
> 31/08/2010 13:00:00
>
> So the first is obviously wrong and I can change the formatting in
> Excel, just not to what I want as that would involve swapping the day
> with the month...
> The second is as I want it and the format cannot be changed in Excel,
> which is fine.
>
> I have read that it may be down to regional settings but I am unable
> to give a fix for this as the CSV files come in from different users
> and we have a load of them that cannot be redone.
>
> I have tried changing the code to paste as values only and various
> other fixes but am so far unsuccessful. I have also read that I could
> convert the CSV's to .txt but I have no experience with this through
> code and would rather not if avoidable.
>
> The code which copies the data is below. Any help would be really
> appreciated. I even started to consider if there is a way to just swap
> the day with the month through VBA as the ones that are correct seem
> to be stuck that way anyway but I have no idea how to accomplish it...
>
> 'Define the SourceRange
> With mybook.Worksheets(1)
> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> 'Now you know where the list (to be copied) ends
>
> Set sourceRange = mybook.Worksheets(1).Range("A2", "A" &
> LastRow).EntireRow 'Sets the range to copy.
>
> End With
>
>
> 'Define where to put the source values
> With basebook.Worksheets("Summary")
> 'Establish the last used row in the target ws
> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> 'Copy the whole lot over starting from row that's =lastRow + 1
>
> sourceRange.Copy
> basebook.Worksheets("Summary").Cells(LastRow + 1,
> "A").PasteSpecial (xlPasteValues)
>
> ' basebook.Worksheets("Summary").Columns("B:C").NumberFormat =
> "mm/dd/yyyy hh:mm:ss"
>
> End With
>
> --
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
