VBA - Force update for date

Question

Hi everyone,

I've currently got some dates which need to be converted from text into an actual date in order to use the =MAX() and =MIN() function. I currently have the following data:

Text Name - 14.01.2018	14/01/2018
Text Name - 09.01.2018	09/01/2018
Text Name - 08.01.2018	08/01/2018
Text Name - 05.01.2018	05/01/2018
Text Name - 02.01.2018	02/01/2018
Text Name - 01.01.2018	01/01/2018

The date column (on the right) is the end result of a formula. I do the following in VBA to get that result:

With RangeG

     .Formula = "=RIGHT(C" & iRow & ",SUM(LEN(C" & iRow & ")-FIND("")"",C" & iRow & ")-3))"

     .NumberFormat = "@"

     .Value = .Value

     .Value = Replace(.Value, ".", "/")

     .NumberFormat = "DD/MM/YYYY"

End With

As you can see I use a formula to get the date from the first column, the result is converted into a Text format (for some reason it sometimes changes into a MM/DD/YYYY format which is not what I want). The value is alter into the cell value, I replace the dot for a slash and then format the cell correctly. However, this doesn't set into place until you select the cell (F2) and then press Enter.

I've decided to go down the "TextToColumns" route however it doesn't work. It won't update the cell. I have the following code:

With RangeG

     .Formula = "=RIGHT(C" & iRow & ",SUM(LEN(C" & iRow & ")-FIND("")"",C" & iRow & ")-3))"

     .NumberFormat = "@"

     .Value = .Value

     .Value = Replace(.Value, ".", "/")

     .NumberFormat = "DD/MM/YYYY"

     ' Force format update

     .Select

     Selection.TextToColumns Destination:=Range("G4"), DataType:=xlDelimited, _

          TextQualifier:=xlDoubleQuote, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End With

Any ideas where I'm going wrong? All I want is the "14.01.2018" text to reformat into "14/01/2018" as a proper date. I can't use the =MAX() and =MIN() formulas until it has.

Many thanks,

Daniel

Answer 1

Not sure how FIND("")"" actually works with the value you show.  For those values, this should work:

    With RangeG

        .NumberFormat = "DD/MM/YYYY"

        .Formula = "=DATEVALUE(SUBSTITUTE(RIGHT(TRIM(C" & iRow & "),10),""."",""/""))"

        .Value = .Value

    End With

If the values are not what you actually show, then get a formula like the one I show that actually works with the strings you have.