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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
693
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try:

VBA Code:
rngCell.Value=cdate(left(rngCell.Value,instr(rngCell.Value," ")-1))
 
Solution

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
sounds like the region settings are not correct or the default format for dates in the cells is wrong
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Check if your system country is set to European or Intgernational date format rather than USA
 

Harry Flashman

Active Member
Joined
May 1, 2011
Messages
356

ADVERTISEMENT

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.

VBA 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

Active Member
Joined
May 1, 2011
Messages
356
Try:

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

Active Member
Joined
May 1, 2011
Messages
356

ADVERTISEMENT

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.

Dot One File Analysis.PNG


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.

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

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

VBA Code:
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
 
Last edited:

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
693
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,480
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,402
Messages
5,624,532
Members
416,032
Latest member
dunhamsasphalt

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