Excel date changed (month and day swapped) after reading and writing with VBA

Question

I wrote two lines of simple VBA codes to read from and write a date to a cell, but unexpectedly the month and day swapped after the run. The steps are:

1. Enter a date "1/11/2017" in the cell A1. The date is now displayed as "01/11/2017".

2. Run the following lines of code:

   Dim s As String
   s = Cells(1, 1).value
   Cells(2, 1).value = s
   

3. The cell B1 now displays "11/01/2017" with the month and day swapped.

My short date format setting in Region in Windows is "dd/MM/yyyy" so the string s stores the value "01/11/2017". However, when writing to the cell, Excel implicitly converts the string s to a date assuming "mm/dd/yyyy" without following the date format setting in Region. I tried to use different short date format settings but that does not change the way Excel converts the string.

So my question is: what could explain the swapping of the day and month? What controls how Excel reads the date string and write to the cell?

Answer 1

Dim s As String
s = Cells(1, 1).Value

s = Cells(1, 1).value will store the value of the cell in a String. Not what it shows in the cell.

For example

If your cell has 11/1/2017 but is formatted to show 01/11/17 then s will store 11/1/2017. This is not a date. It is a String. If you do not believe me then try this

Dim s As String
s = Cells(1, 1).Value
Debug.Print s + 1 '<~~ You will get a Type Mismatch error

Now try this

Dim s As Date
s = Cells(1, 1).Value
Debug.Print s + 1 '<~~ You will get a proper date

==> When you are trying to store a String which contains a date to a cell which has General format then Excel will convert the string to date in a format what it feels is best (based on regional settings). And this is what is happening in your case.

Resolution:

1 Declare a Date variable and then store the value in that. Ex: Dim s As Date

OR

2 Convert the string to a date and then store it. Excel will not change it. And that is what DateValue(s) does.

Some Testing

Let's take these 3 scenarios

Sub SampleA()
    Dim s As Date
    s = Cells(1, 1).Value
    Cells(2, 1).Value = s
End Sub

Sub SampleB()
    Dim s As String
    s = Cells(1, 1).Value
    Cells(2, 1).Value = DateValue(s)
End Sub

Sub SampleC() '<~~ As Suggested in the other answer
    Dim s As String

    s = Cells(1, 1).Value

    Cells(2, 1).NumberFormat = "dd/MM/yyyy"
    Cells(2, 1).Value = s
End Sub

Screenshot