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.

 

[Harry Flashman]

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.

The array in this case (my second example) is of the string data type. It only gets converted into an Excel date as it is outputted onto the worksheet. Excel does that automatically. But it should convert the string to an excel date according to the regional settings of my computer. And it isn't. It's weird.

What I could trying doing is formatting the destination as text before outputting my data. But that is not ideal.

The problem I think is a bug with my installation of Excel.

It's annoying because I have a lot of routines that work with dates and it will be such a hassle to rewrite so many routines that I have been using for years without a problem.

I might try uninstalling Office and reinstalling and see if that fixes the problem. Unless I can find another solution (that doesn't involve rewriting a lot of code).

 

[Harry Flashman]

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

Thanks Dave. That works well.

 

[dmt32]

Thanks Dave. That works well.

Glad suggestion helped - appreciate feedback

Dave

 

[JLGWhiz]

I have tried to replicate the results on my computer. It does not change the regional format and only ignores the cell content if 'PM' is not found in the cell. I am using xl2013 on WIN 10.

 

[Harry Flashman]

I have tried to replicate the results on my computer. It does not change the regional format and only ignores the cell content if 'PM' is not found in the cell. I am using xl2013 on WIN 10.

Thanks for testing. I have tested this on another PC and I am not seeing this problem. It's just this installation of Excel. I am going to uninstall and reinstall and see if that helps.

 

[Harry Flashman]

I spoke too soon. I get different results on with my first routine on a different computer (that I posted the code for). But my other routine where I shared a screenshot of an array being filled does actually output the dates as American. It's weird because the "date" is just a text string and the array itself is declared as a string. So I would have thought Excel would have just outputted the text as written and then convert the text to a date according to the regional settings of the PC. But this appears not to be the case. I am surprised I have never notice this before.

 

[JLGWhiz]

Thanks for testing. I have tested this on another PC and I am not seeing this problem. It's just this installation of Excel. I am going to uninstall and reinstall and see if that helps.

Well, the results you are getting are completely illogical and point to a corrupted processor, in my opinion. The value in the cell should be completely ignored by the Replace function if the 'FindWhat' value is not in the text of the cell. That is built into the vba underlying program. So if the compiler is seeing something that makes it execute an action on the cell, then it seems to me that it is corrupt. But, I am not a qualified tech and have no idea how to validate my theory other than replacing the processor, and I won't recommend that. Maybe a reinstall will clear it up. Good luck.
Regards, JLG

 

[Harry Flashman]

Well, the results you are getting are completely illogical and point to a corrupted processor, in my opinion. The value in the cell should be completely ignored by the Replace function if the 'FindWhat' value is not in the text of the cell. That is built into the vba underlying program. So if the compiler is seeing something that makes it execute an action on the cell, then it seems to me that it is corrupt. But, I am not a qualified tech and have no idea how to validate my theory other than replacing the processor, and I won't recommend that. Maybe a reinstall will clear it up. Good luck.
Regards, JLG

I agree it is bizarre. I have googled this, and I see I am not the first person to encounter this problem; I see on other forums people referring to this as an Excel bug (that doesn't everyone for some reason). Normally the second Replace function is ignored completely. As to why on earth this line should switch the regional setting of the date is bizarre.

However as illogical as that is, I see Excel VBA is a bit inconsistent with the way it treats dates as text. I posted a screenshot from another routine that reads the contents of text file. Outputs that content to a worksheet and then processes the data outputting to another worksheet. One of the fields is a date but formatted as text. It gets loaded into an array of the string data type, but when it is outputted, it outputs the date as if it were American. And it does this my other systems too. I have never noticed this before (the date field has never been that relevant for my purpose just to check the contents of a data file that I process with other software altogether).

So in this other routine the array loads a date as text; eg, 08-01-2021 (8th of January), but when the array value is outputted to the worksheet it gets immediately converted by Excel to 01-08-2021 (1st of August) even thought my computer's regional is set to Australia.

However I have fixed this problem using a tip that Dave gave earlier in the thread.

So this line of the array contain the date. If you look in the array the value is "08-01-2012" but it gets outputted as 1/08/2021
rngOut.EntireRow.Cells(5).Value = arr(1, iDate))

However if modifiy my code accordingly
rngOut.EntireRow.Cells(5).Value = DateValue(arr(1, iDate))

THEN it correctly outputs the date as 8/01/2021.

So I'll keep this in mind for the future.