VBA - Converting Date as String To Excel Date (something strange)

[Harry Flashman]

I have some code that I wrote ages ago that used to work well but lately it is playing up for some reason and not behaving in a way I would have expected.

I have a value in a cell. It looks like a date but really it is a text string. The value always ends in AM or PM. Here is an example.
06-Jan-2021 08:05AM

I want the value to become a proper Excel date, but without the time (British date formatting, d/mm/yyyy)
6/1/2021

This the routine that I am using.

Sub FixDates()

Dim rng As Range, rng2 As Range, rngHRow As Range
  
    Set rng = Range("a1").CurrentRegion
    Set rng2 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Cells
    Set rngHRow = rng.Rows(1).Cells
  
    Dim i As Integer, rngC As Range, rngCell As Range
    i = WorksheetFunction.Match("Date", rngHRow, 0)
    Set rngDate = rng2.Columns(i).Cells
  
    For Each rngCell In rngDate
 
        rngCell.Value = Replace(rngCell.Value, "AM", " AM")
        rngCell.Value = Replace(rngCell.Value, "PM", " PM")


        rngCell.Value = WorksheetFunction.RoundDown(rngCell.Value, 0)
        rngCell.NumberFormat = "d/mm/yyyy;@"

    Next rngCell


End Sub

The problem is because of these two lines of code.

rngCell.Value = Replace(rngCell.Value, "AM", " AM")
rngCell.Value = Replace(rngCell.Value, "PM", " PM")

In our example:
06-Jan-2021 08:05AM

The first replace function converts the text value into a date.
6/01/2021 8:05:00 AM

The second line however should not do anything because the text string "PM" does not exist in this particular cell. However something strange has started happening, and this never used to happen. It is switching the day and the month.
1/06/2021 8:05:00 AM

So the date is now 1 June 2021 when it should be 6 of January 2021.

I can rewrite my code to fix this bug, but I am curious as to why it might be happening.

I am using Excel 2013.

If anyone has an idea why this is happening I would be interested to hear your thoughts.

 

[eduzs]

Try:

rngCell.Value=cdate(left(rngCell.Value,instr(rngCell.Value," ")-1))

 

[diddi]

sounds like the region settings are not correct or the default format for dates in the cells is wrong

 

[JLGWhiz]

Check if your system country is set to European or Intgernational date format rather than USA

 

[Harry Flashman]

Check if your system country is set to European or Intgernational date format rather than USA

My computer's regional settings is set to Australia and we use the dd/mm/yyyy format just like Britain. Normally I don't have an issue with dates. This seems like a bug to me.

rngCell.Value = Replace(rngCell.Value, "AM", " AM")
rngCell.Value = Replace(rngCell.Value, "PM", " PM")

The first line above converts the text value to a date, just by putting the space before AM (assuming text string has AM instead of PM).
The second should not do anythinng unless the string contains "PM. But for some reason this line switches the date region. It's really odd.
I have been using this code for years without a problem.

I have fixed it using a conditional. But it seems weird to me that I had to adjust my code.

j = InStr(rngCell, "AM")
       
        Select Case True
            Case j > 0
                rngCell.Value = Replace(rngCell.Value, "AM", " AM")


            Case j = 0
                rngCell.Value = Replace(rngCell.Value, "PM", " PM")
               
        End Select

 

[Harry Flashman]

Try:

rngCell.Value=cdate(left(rngCell.Value,instr(rngCell.Value," ")-1))

That's great line of code. Does it all in one step (almost). Perfect. Thank you.

Still curious as to why my original code cause the date to switch regions (for some cells, not all).

 

[Harry Flashman]

I have discovered another routine that I wrote where this date region problem is happening. This routines reads the data from a text file and then processes and outputs the data to a worksheet.
First I put all the data from text file into a two dimensional array. Once I have filled the array I then output the data on to a worksheet. However I when the date is outputted it is switching the day and the month around.

Here we see inside the array and the date is 8 January 2021. BUT when I output the data for the date field the day and month gets swapped around.

rngOut.EntireRow.Cells(5).Value = arr(1, iDate)

So instead of outputting 8/1/2021 my routine outputs 1/8/2021. That is it changes the month from January to August.

And yet if I enter a date in Excels special number system and then format the date, it formats that date British style, which is inline with my PC's regional settings.

Thus
44317 is 5-May-21.

Any idea what might be happening here? This is rather frustrating.

 

[Harry Flashman]

I just switched my PC from Australian regional settings to USA regoinal settings. Now my PC calendar says the date is 2/10/2021 (2 Feb 2021), mm/dd/yyyy.

So I run my routine and it output the date in the US format, but it is stil mixing up the day and the month (to be expected I suppose).

I changed back to Australia and the problem persists. VBA seems to be reading dates as if they were US dates.

And yet if I run this routine to check the region format it says my date format is dd/mm/yy.

Sub RegionalDateFormat()
    Dim DateOrder As String
    Dim DateSeparator As String
  
With Application
    Select Case .International(xlDateOrder)
        Case Is = 0
            DateOrder = "month-day-year"
        Case Is = 1
            DateOrder = "day-month-year"
        Case Is = 2
            DateOrder = "year-month-day"
        Case Else
            DateOrder = "Error"
    End Select
  
    DateSeparator = .International(xlDateSeparator)
End With

Debug.Print "Date Order: ", DateOrder
Debug.Print "Date Separator: ", DateSeparator
End Sub

 

[eduzs]

If your spreadsheet has a date as a string of many parts of the world, how will Excel know when it comes from a place with dd-mm or mm-dd? If there is a column that returns the original country or date format ...
I suppose the problem is not with your computer's date format setting.

 

[dmt32]

You could try using DateValue Function in your code as it recognizes the order for month, day, and year according to the short date format in your system and also, will ignore any time values that may be included & just return a date.

Try this update to your code

Sub FixDates()
   
    Dim rngCell     As Range, rng As Range
    Dim i           As Variant
   
    Set rng = Range("a1").CurrentRegion

    i = Application.Match("Date", rng.Rows(1), 0)
    If IsError(i) Then Exit Sub
   
    For Each rngCell In rng.Columns(i).Cells
        With rngCell
            If IsDate(.Value) Then
                .Value = DateValue(.Value)
                .NumberFormat = "d/mm/yyyy"
            End If
        End With
    Next rngCell
   
End Sub

Dave