Date format dd/mm/yyyy read as mm/dd/yyyy

Question

I have a spreadsheet with a column formatted as:

Category: Date
Type: *dd/mm/yyyy
Location: UK

When I read the data in this column via VBA, it reads in the format mm/dd/yyyy.

For example, 10/06/2014 (10 June 2014) is reading 06/10/2014 (06 Oct 2014).

My code: sDate = SourceSheet.Range("AB" & CurRow.Row).Value

Please can you post code to go with? This may help solve your issue — bmgh1985, Jun 16 '14 at 12:18

score 3 · Accepted Answer · answered Jun 16 '14 at 13:47

I have this issue with my forms too and the best method for me is to format the textbox like this:

sDate = format(SourceSheet.Range("AB" & CurRow.Row).Value, "mm/dd/yyyy")

Even though the date format is wrong in VBA, it seems to work the right way round in Excel. It's weird, I can't explain why it happens, but this fixes it for me. Whenever I go from VBA to Excel, I almost always find this issue if the value is stored as a date.

Gary's Student · Answer 2 · 2014-06-16T12:41:36.763

2

Consider:

Sub luxation()
    Dim sDate As Date, CurRow As Range
    Set SourceSheet = ActiveSheet
    Set CurRow = Range("A1")

    ary = Split(SourceSheet.Range("AB" & CurRow.Row).Text, "/")
    sDate = DateSerial(ary(2), ary(1), ary(0))

    MsgBox Format(sDate, "dd mmmm yyyy")
End Sub

edited Jun 16 '14 at 12:41

answered Jun 16 '14 at 12:34

Gary's Student

95,722
10
59
99

Thanks for your reply. Unfortunately, it doesn't work. I realise that the problem occurs when I am reading the cell from another workbook, i.e when I record a macro in the workbook containing the date, it displays the correct date. But when I open the workbook and read the date from another workbook containing the macro, it fails. But I still cannot out why it fails!! – Salim Jun 16 '14 at 13:29
@Salim Did it give an error message? I would expect it to. Did you try actually converting the date parts from strings to integers (as the DateSerial function wants)?? sDate = DateSerial(CInt(ary(2)), CInt(ary(1)), CInt(ary(0))) – Mark Balhoff Jun 16 '14 at 14:28

score 0 · Answer 3 · edited May 23 '17 at 12:32

This question of mine - .NumberFormat sometimes returns the wrong value with dates and times - gives some background which may help.

I first encountered this VBA bug many years ago and it is worse than it seems. I noticed that many - but not all - dates in a worksheet that I had been updating for a year were wrong. It took me a long time to diagnose the problem. Those dates that could be interpreted as middle endian dates had been corrupted but those that could not be interpreted as middle endian dates were unchanged. So 12/06/2014 will become 6 December but 13/06/2014 will remain 13 June. If 13/06/2014 had been rejected as an invalid date or left as a string, I would have spotted the error immediately. The dual interpretation so every date was imported as a date - the wrong date but still a date - ensured I did not notice until much later maximising the cost of correcting for the bug.

Excel holds dates and times as numbers. "17 June 2014" is held as 41807 and "1 January 1900" is held as 1. In both cases, the value is the number of days since 31 December 1899. Times as held as a fraction:

number of seconds since midnight
--------------------------------
      seconds in a day

So 06:00, 12:00 and 18:00 are held as 0.25, 0.5 and 0.75.

This bug is encountered when the transfer of a date involves a conversion to and from string format. I have not discovered a single case in which the conversion from date to string has been wrong. It is the conversion from string to date that hits this bug.

I can see that SilverShotBee's solution will avoid the bug but it would not appeal to me. I no longer use any ambiguous dates ever.

One choice is to transfer the value as a number. If cell A3 contains the date and time "17 June 2014 9:00" then CDbl(Range("A3").Value) returns 41807.375. When you store this number in a cell you will need to set the cell's NumberFormat to the date format of your choice but that might be a good thing.

If I were going to use middle endian dates, I would be explicit. #13/06/2014# is always interpreted as middle endian.

I prefer unambiguous strings. "2014-06-13" or "13 June 2014" are not misinterpreted by VBA or by a human reader.

score 0 · Answer 4 · answered Oct 01 '16 at 20:14

0

Have just come up against this issue! Reading records from a .csv and storing in an .xls

I found the following sequence works to overcome the misinterpreted dates:

Read the date field from the .csv file
Store it into a cell in the .xls file
Read it back into vba
Store into its required destination in the .xls

Date is in original format

answered Oct 01 '16 at 20:14

Phil

1

Welcome to stack overflow. Your answer doesn't seem to fully answer the question of "how to prevent 10/06/2014 from being read as 06/10/2014". You touch on it in step 3 "Read it back into vba", but it needs more specifics of how this should be done to prevent the error the question covers. – Thaddeus Albers Oct 01 '16 at 20:42

score 0 · Answer 5 · answered Oct 08 '16 at 12:56

I found this issue to be incredibly complex and was trying to keep it as simple as possible but have indeed left a few vital details out! Apologies. Here is a fuller version of what I found:

First of all I should explain I was reading dates (and other fields) from a .csv and storing back into an .xls

I am on Office 2002 running on Windows/7

Using 2 example dates: 27/4/2015 and 7/5/2015 in dd/mm/yyyy string format (from the csv)

What I found was:

Reading the 27/4/2015 text date field from csv into a variable dimensioned as STRING and storing into an xls field in dd/mm/yyyy DATE format produces a cell that reads 27/4/2015 but converting it into a cell formatted as Number also produces 27/4/2015. 7/5/2015 on the other hand produces a string that reads 7/5/2015 and converting it into a cell formatted as Number produces 42131.

Reading the 27/4/2015 text date field from csv into an undimensioned variable and storing into an xls field in dd/mm/yyyy DATE format produces a cell that reads 27/4/2015 but converting it into a cell formatted as Number also produces 27/4/2015 while 7/5/2015 reads 5/7/2015 and converting it into a cell formatted as Number produces 42190.

Reading the 27/4/2015 text date field from csv into a variable dimensioned as DATE and storing into an xls field in dd/mm/yyyy DATE format produces a cell that reads 27/4/2015 and converting it into a cell formatted as Number produces 42121. 7/5/2015 on the other hand produces a string that reads 5/7/2015 and converting it into a cell formatted as Number produces 42190.

The first 3 scenarios above therefore do not produce the desired results for all date specifications.

To fix this I do the following:

Input_Workbook.Activate
ilr = Range("A5000").End(xlDown).End(xlDown).End(xlDown).End(xlUp).Row
For i = 1 To ilr
  Input_Workbook.Activate
  If IsDate(Cells(i, 1).Value) Then
    d1 = Cells(i, 1).Value
    d1 = Replace(d1, "/", "-")
    ThisWorkbook.Activate
    Cells(14, 5).Value = d1
    d1 = Cells(14, 5).Value
    If VarType(d1) = vbString Then
     d1 = CDate(d1)
    End If
    Cells(i, 1).Value = d1
  End If
Next

The cell used to store the date initially is formatted GENERAL and the ultimate target cells is formatted as DATE (dd/mm/yyyy).

I don't have enough brain cells left to fully explain what happens to the dates during this process but it works for me and of course the choice of target cells is completely random in the above code block.

score 0 · Answer 6 · edited Aug 07 '18 at 00:22

0

The problem was VBA was opening the csv with the reverse dates for single digit days.

This way of opening the workbook worked the same as when I did it manually so had the correct dates in dd/mm/yyyy format. Then copied across correctly:

Workbooks.OpenText FileName:=fpathO, datatype:=xlDelimited, comma:=True, local:=True

edited Aug 07 '18 at 00:22

Stephen Rauch

47,830
31
106
135

answered Aug 07 '18 at 00:02

Mark

1

Date format dd/mm/yyyy read as mm/dd/yyyy

6 Answers6