Date format, VBA, and TXT/DAT

Dates are not my favourites,

however, Excel does translate 12.9.2006 into a correct date format using the Text to Columns 'Date = DMY' setting, which is what you appear to require.

How a date is displayed is largely irrelavant, it is, of course, stored as a 5.~~ digit number and displayed as requested, 12.9.2006 being 38981

To assist you in setting your program, (my old standby) to start recording a macro, select your column of dates, then Data, Text to columns, next, next, date=DMY and Finish. The settings in the macro should be what is required in your code.

Hope this helps
---

Originally Posted by Potoroo

Hello all,

I have spent a lot of time reading posts about the way VBA uses the US date format in Excel but, apparently, I keep getting this wrong.

I extract data from a SAP application. The filename when extracted currently defaults to using a .xls extension but is, in fact, a CSV file. I have renamed the file to both .txt and .dat extensions based on suggestions for forcing Excel to allow the reformatting of the dates in dd/mm/yyyy format but I am still seeing mm/dd/yyyy in the end result. Note: in CSV format, the date delimiters are dots i.e. 1 September 2006 will show as 01.09.2006.

I recorded code when performing a manual opening of the file and then amended the Array "y" format for the date columns, added in the Local:=True bit and am certain the local format in my Excel is dd/mm/yyyy.

I turned on screen updating to watch what happens when I run the code. Part of the formatting involves replacing the dot delimiters with / delimiters and then applying the date format dd/mm/yyyy. It seems that this is where the dates swap around to show as mm/dd/yyyy.

Please help.

First bit of code:
Workbooks.OpenText Filename:="C:\MyFile.dat", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), _
Array(3, 4), Array(4, 4), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 4)), TrailingMinusNumbers:=True, Local:=True

Additional formatting bit of code:
Columns("C:D").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.NumberFormat = "dd/mm/yyyy;@"

Many thanks.

Hi,

Are you referring to a date exported to .csv?
or a date Imported from .csv?

For the imports you seem to have it solved, for the Export to csv this is also simple ( )

For the field to be output, format the cell as Date,
(the tricky bit)
In Dates there is a *14/03/2001
then third item, another 14/03/2001
then eighth item, another 14/03/2001

use the eighth item 14/03/2001

Save your file to .csv

voila

---

Originally Posted by Potoroo

Hello Bryan,

Thank you for trying to assist but I have already tried your suggestions and still have the issue. I realise that Excel treats dates as numbers but, because I inhabit a world of end users who are expecting to see dates in a form they are used to dealing with, they tend not to question where a date is displayed as mm/dd/yyyy and then transfer this information to other systems as if it was dd/mm/yyyy. The end result is that other systems then record an incorrect date. In some areas, this is causing duplication of things which have the potential to be a security risk for our company.

Ideally, I want to run my formatting macro and end up with the date supplied in a format considered correct by my company. This is dd/mm/yyyy.

As I said in my earlier post, it only seems to swap the format around once the code applies the date formatting. Although the code clearly shows I want dd/mm/yyyy, the effect is that Excel takes over and makes if mm/dd/yyyy.

Can anyone assist, please?

Hi,

My knowledge of VB isn't that hot, but, if you

    Selection.NumberFormat = "dd/mm/yyyy;@"

do you still not display leading zeros?

---

Originally Posted by Potoroo

Hello again Bryan,

I must not be explaining myself very well.

I have imported a .csv file to Excel. The dates are displayed as dd.mm.yyyy in the csv file. When I run my formatting macro in Excel to clean up the report and run some checking procedures, I also want to reformat the dates to display in our preferred format of dd/mm/yyyy.

I turned on screen updating so I could see when the date formatting changes to the US style of mm/dd/yyyy as the macro runs through its steps. My macro replaces the dot separator with a forward slash separator so that Excel knows this should be read as a date.

After the slashes have replaced the dots, the date displays as dd/mm/yyyy. BUT, (I know this is nit-picky), if there is a zero in the day or month parts of the date, Excel drops it. I want to display these zeros. In order to do this, I have tried to apply formatting as dd/mm/yyyy. It is at this point any dates with days or months <= 12 turn completely around to show in the US format mm/dd/yyyy.

How do I stop Excel imposing the US format even though I have used VBA to apply the rest of the world's format?

Appreciate any suggestions.

I would think that the removing of the / actually turns the dates into an american date you may think it is d/m/yy but actually it is m/d/yy and this only becomes appartent when you change the format to dd/mm/yyyy

It is often safer to use dd-mmm-yyyy as a format as then it is easy to see what the dates are and avoid the confusion

Why not take more control of the date, when it is converted from text and use a formula to make sure it is in the format you require eg =DATE(RIGHT(text string,4),MID(text string,4,2),LEFT(text string,2))


then lose the original dates

Regards

Dav