Excel VBA ActiveCell formula convert to value

Select the cells and run:

Sub DateFixer()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
        For Each r In Selection
            v = r.Text
            r.Clear
            r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
            r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), Left(v, 2)) + TimeSerial(Mid(v, 12, 2), Mid(v, 15, 2), Right(v, 2))
        Next r
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

If you have a lot of data you could use this simple VBA method which I believe goes after what you want, or at least points you in the right direction. First of all you can't display the formula and have it calculate without duplicating the cell

If you can get past that part then the next step is to either manually copy each cell and display it as text or use a VBA method like below to do it for you.

I created this sub to show you how to get the formula property of a cell:

Public Sub test()
Range("C1").NumberFormat = "@"
Range("C1").Value = Range("B1").Formula
End Sub

I simply put that formula into a side column as text:

You could of course apply a loop to this to go through an entire sheet.

The other way to achieve what you are looking for is to understand that my argument Range("B1").formula can be set or retrieved. This means that hypothetically you could do it backwards:

dim value as string
value = Range("B1").formula

..then you could use the variable value in additional formulas allowing your formula to be displayed and calculated at the same time.

Hopefully this helps you along your way.