Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4202
  • Last Modified:

Intermittently getting day/month swapped when copying dates from Excel 2003 to a CSV text file using a Excel VBA macro


I run a VBA macro to copy invoice data from an Excel (2003) worksheet to a CSV file for me to then later import to my accounting package. This imported data consists of figures and dates…

The problem is that every once in a while we will find the day and month of the imported data (within the CSV file) has been swapped around. It’s an intermittent problem that cannot be attributed to changes made, to the macro and/or excel template, between a valid date import & invalid / swapped date import.
i.e. the date in Excel reads – 02/10/2004, but sometimes when its imported it would read 10/02/2004 ??? It can happen 2 out of 10 times in a batch and next time without any problems…

 VBA steps:
1. First we replace the date in the particular sell with a value to make the date permanent & unchangeable for the specific invoice:

    Selection.PasteSpecial Paste:=xlValues  'Replace the contents with the actual Invoice Date permanently
    Application.CutCopyMode = False

2.We copy the range of sells containing the valid data to a CSV file, ready for importing into our accounting package:

   'Copy the remaining rows of the matrix to the summary, for transfer to MYOB
    Range("BA13:CO20").Select 'All the data left in the invoice matrix area
    ChDrive "S:"
    FileRequired = "CP_Summ.TXT"
    Selection.End(xlUp).Select          'Find last data row in Column A
    ActiveCell.Offset(2, 0).Select      'Select 2nd clear row below it
    Selection.PasteSpecial Paste:=xlValues
    If Cells(2, 1) = "" Then            'Cell A2
        Rows("2:2").Select              'Row 2 should not be a blank row
        Selection.Delete Shift:=xlUp
    End If

   'Save Summary, TAB Delimited format for MYOB
    ActiveWorkbook.SaveAs filename:=FileRequired, FileFormat:=xlText, CreateBackup:=True
    ActiveWorkbook.Close                'Close the Tab delimited TXT file in MYOB directory

If anyone has any ideas, please help… as you can imagine it can have huge implications when invoice data gets imported with the wrong dates.

  • 6
  • 4
  • 3
  • +1
3 Solutions
i've faced the same probs. but within MS Access and VB6. So i decided to use the string variable instead of using date variable.
and i did it with long date (using format "dd MMMM yyyy"), if i want to use the date value i'll convert the string to date value...(1/March/2004 can't be assumed as 3/January/2004 if u use the conversion), so it will always be 1/3/2004 (in dd mm yyyy) or 3/1/2004 (in mm dd yyyy).
Further more to be precise..
here are the links which deals with multiple version of MS Office products...

Hope this answers ALL your questions.
Cheers. Bye
An alternative and simple and locale independent method is to change your date formats to numeric (0 dec places) - or General will probably do before exporting. This will, for example give 38272, 38331 respectively for 10 February 2004 and  2 October 2004.

If the app that reads the csv is in VB and uses the CDate function it will first try and read in based on the machine's locale, and failing that it will swap day and month around. e.g. using my locale settings (dd/mm/yyyy).

CDate("10/02/2004") gives 10/02/2004 (10 Feb 2004)
CDate ("31/12/2004")  gives 31/12/2004 (31 Dec 2004)  
CDate ("12/31/2004")  gives 31/12/2004 (31 Dec 2004)  - oops.

To get to the root of the problem - not just work around it - first look at the dates that are failing are do they follow a pattern e.g. day numbers greater than 12?

Consider looking at the locales on your machines are.

Remember that passing dates using strings is inherently dangerous due to formatting and locale differences
You might also consider using the "independent" internet date format - but you will have to translate it at either end - I've got some VB code somewhere to do it.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

what if the user machine setting differ from the designer locale settings?..i've faced this things so many times, and for me using the long date (dd MMMM yyyy) is the best solution. there will be NO error if the locale setting is different between both user and designer. (these are my experience)

>> what if the user machine setting differ from the designer locale settings?..i've faced this things so many times, and for me using the long date (dd MMMM yyyy) is the best solution. there will be NO error if the locale setting is different between both user and designer. (these are my experience

I disagree, because if the MMMM is not the same language on both locales it will fail.

For example I was working in a department and we asked our customers to send us orders in a CSV the dates being in ddmmmyyyy format. This worked until we got an order from Turkey which failed.

e.g. An English date in dd MMMM yyyy format: 25 December 2004. The same date using the same format and Malaysian (Malay) locale settings gives 25 Disember 2004. In both locales, the date.

If passing the date as the integer part of a number, you can avoid all locale problems (see above) as long as both applications use the same numbering system. In excel, the dates/times are held in 32bit real numbers (as is VB). The integer part is the date (number of days past a given date), the decimal is the time past midnight. The value of each number is the same for a given date/time regardless of the system locale.

I use the w3c standard - which is derived from the international standard ISO 8601 date standard.

This is the code I use nowadays - for XML in and out of VB apps. The date :

Public Function VBDate2XMLDate(ByVal VBDate As Date) As String
    VBDate2XMLDate = Format$(VBDate, "YYYY-MM-DD\THH:NN:SS")
End Sub

Public Function XMLDate2VBDate(ByVal xmlDate As String) As Date
    Dim sDateTime() As String
    Dim sDateParts() As String
    Dim sTimeParts() As String

    sDateTime = Split(xmlDate, "T")
    sDateParts = Split(sDateTime(0), "-")
    sTimeParts = Split(sDateTime(1), ":")

    XMLDate2VBDate = DateSerial(CInt(sDateParts(0)), _
                     CInt(sDateParts(1)), CInt(sDateParts(2))) + _
                     TimeSerial(CInt(sTimeParts(0)), _
                     CInt(sTimeParts(1)), _
End Function

The issue goes further for this user.  Fatboy is using a great method for serializing a date so that it doesn't get warped by the users local settings and VB's sometimes insufficient formatting procedures, but I assume that these dates are being entered by hand into an Excel spreadsheet...  This is where the problem occurs as Excel will automatically convert the date to a numeric value based on local settings before any of his VBA code ever sees the date!

Try it out:  Open a new sheet and place today's date 9/23/2004 into a cell then copy it and paste the VALUE into another cell (as he is doing programmatically) and you'll see 38253.  Use something like 3/2/2004 and the number actually stored will vary depending on local settings.
>> Use something like 3/2/2004 and the number actually stored will vary depending on local settings

Yes I understand this - but you have to assume that the user is typing in the dates in the locale that their machine is in. For instance if I type the date on my machine (UK locale) as 3/2/2004 I mean 3 Feb 2004. If I wanted to enter the same date and my machine was US locale I would have to enter 2/3/2004. If I get this the wrong way around then there are only four possibilities:

1. My system has the wrong locale settings;
2. I have typoed;
3. I am stupid.
4. All of the above

Unfortunately when we pick up the data, there is nothing we can do about it - or detect it.

PS I suspect it is number 3.
PS I was right - it was 3. I see what you mean now I'm reading again. And yes set the format to dd mmm yyyy in excel so that when the user types in they know what they are typing in. Yes. Then serialise the date and send it to the CSV.
Yes, #3 is possible.  Is it not also possible that a shared sheet, across locals, might get the date typed in by someone with UK settings in the US format for consistency's sake (assuming there are explicitly formatted dates in the sheet somewhere).  Most users would never realize that there is something other than a few numbers and some dashes stored and wouldn't realize that a date typed in that way is actually interpretted by the computer to be a different date.  People think that what they type into Excel is static unless they put in a formula.

Yes this is true. And more to point at number 3. But this would be a poorly thought out design - and the only solutions (I can think of off the top of my head) would be not to explicitly format dates to a specific locale in this situation (use "short date" etc); or enter the cells as strings and convert the strings to dates using a formula/macro elsewhere.

My own view on software design is to respect people's system settings. If they want black writing on a black background, then that's their lookout.

>> People think that what they type into Excel is static unless they put in a formula.
Yes, if these things were completely simple what would I do for a living when people got themselves into trouble?
Agreed, Fatboy.  Most of this discussion probably did nothing by satisfy our own interests though.  There is more than enough information here for the question's author to figure something out or re-ask her question with a few more details.  I don't think that we can acheive anything more until the author makes another visit to the discussion.
ya...it seems like i prefer your method FatBoy....i forgot about the difference in long date for different country.
what about this situation?...one user locale setting is mm/dd/yyyy and other user locale setting dd/mm/yyyy. and they are passing the value to a database. in this case if they are using  their own locale setting then the database will store using its locale setting. is there any where to standardize it?.....i dunno about the ISO that said before.
>> what about this situation?...one user locale setting

This is covered using my previous example:

The database should store dates as datetimes or dates (locale independent) not strings. In most DBMSs date/datetimes are Integer/Real real numbers.
The integer component contains the number of days after an arbitary start date, and the decimal component the time as a proportion of 24 hours i.e. 0.25 -> 0.25*24 hours = 6 hours -> 6 O'clock in the morning.


A user in Locale 1  enters a date in mm/dd/yyyy format: 11/03/1966  (3 November 1966). It is stored on the database as 24414 (RDBMS dependent).
A user in Locale 2 using the same RDBMS enters a date in dd/mm/yyyy format: 03/11/1966  (3 November 1966). It is stored on the database as 24414.

The number 24414 is locale independent unfortunately it is not RDBMS dependent. Oracle will do it differently from SQL/Server. SQL/Server differently from Access. etc. My example, 24414 is the excel representation for that date.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now