DateValue is leaving some dates as strings-CodePudding

Using Office 365 - but some users access files via older versions. Standard and system date format is DD/MM/YYYY. Original data also formatted to look this way.

I have a range of cells that appear as dates, but some are formatted as text. The data comes from a system extract so I can't help the way it is formatted originally.

I use this data to create automated reports using VBA, but for some reason DATEVALUE seems to leave some of the dates as strings.

I have tried coding it several different ways but cannot get this small piece of it to work and I keep getting cells still as text.

If I apply a formula manually in the sheet, it works. I have checked by applying breaks, and that line of code is running (i.e. the IF statement criteria is being met where is should be).

Since the code moves data from one sheet to another, I know all the variables are correct and everything else works perfectly except this.

See below for more info:

Code:

        If Replace(Field32, "/", "") <> Field32 Then
            Field32 = DateValue(Field32)
        End If

        If Replace(Field34, "/", "") <> Field34 Then
            Field34 = DateValue(Field34)
        End If

I have tried CDate. I have also tried formatting field first using Format(Field32, "dd/mm/yyyy"), I have tried a couple of different ways of checking if the field is already a date. I tried converting all to text and then applying DATEVALUE to every row, it didn't work, just got all text. I have also tried just applying DATEVALUE to every row, but it seems like DATEVALUE is not working at all as it didn't return errors where there are already dates.. What am I doing wrong?

Data After Code has executed:

As you can see, the dates stored as text are still stored as text and as a result some other calculation fields don't work

With fields formatted as number to make it clear to see which ones are now dates:

CodePudding user response：

For example:

    Dim c As Range, arr
    
    For Each c In Range("B25:B33").Cells
        'if formatted as text and content looks like a date
        If c.NumberFormat = "@" And c.Text Like "##/##/####" Then
            arr = Split(c.Text, "/")
            c.NumberFormat = "dd/mm/yyyy"   'set format *before* value
            c.Value = DateSerial(arr(2), arr(1), arr(0))
        End If
    Next c