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

Harry Flashman

Active Member
Joined
May 1, 2011
Messages
356
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.

VBA Code:
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.
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

Active Member
Joined
May 1, 2011
Messages
356
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).
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Harry Flashman

Active Member
Joined
May 1, 2011
Messages
356
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


VBA 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.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
May 1, 2011
Messages
356

ADVERTISEMENT

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

Active Member
Joined
May 1, 2011
Messages
356
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
May 1, 2011
Messages
356
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,463
Members
416,029
Latest member
CSM1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top