Change date format so that DAYS() formula work

Question

I'm trying with VBA to convert a date format in a table column so that I can use it in formulas.

The column contains dates with format "general", not "date" and looks like this: 13/04/2019. So it needs to be dd/mm/yyyy and recognized as a date.

I've looked up online and struggle to find something that works. I've tried:

.numberformat = "dd/mm/yyyy"

but it didn't work

This is the code I'm trying.

    With wsDormant.ListObjects("Table_Dormant_Stock").ListColumns.Add
        .DataBodyRange.Formula = "=DATE(RIGHT([@[Days Last Sold]],4), MID([@[Days Last Sold]],3,2), LEFT([@[Days Last Sold]],2))"
    End With

'Change date to amount of days
    With wsDormant.ListObjects("Table_Dormant_Stock").ListColumns.Add
        .DataBodyRange.Formula = "=DAYS($C$8,[Days Last Sold])"
         wsDormant.ListObjects("Table_Dormant_Stock").ListColumns("Days Last Sold").DataBodyRange.Value = .DataBodyRange.Value
        .Delete
    End With

I'm getting #value when I try the formula. It needs to convert the cell format to date. The formulas that uses dates don't seem to like the date format in dd/mm/yyyy. But are happy with yyyy/mm/dd.

Gary's Student Gary's Student · Accepted Answer · 2019-04-23T12:28:35

You should first convert your "pseudo-dates" into "real" dates. Select the cells and run:

Sub FixDate()
    Dim d As Date, r As Range, v As String

    For Each r In Selection
        v = r.Text
        r.Clear
        arr = Split(v, "/")
        d = DateSerial(arr(2), arr(1), arr(0))
        r.Value = d
        r.NumberFormat = "dd/mm/yyyy"
    Next r
End Sub

Change date format so that DAYS() formula work

1 Answers