Dislpay string text as date automatically when I copy this data from Microsoft projects to excel

[Terasmus]

Hi When I copy the dates over from Projects into Excel, it displays the dats as a string text.
How can I make it so that when I copy this data over, there is a formula on these cells that automatically changes this to the date and time format like the bottom arrow?
Thanks for your help, if this needs to be done in a macro, please explain that.

 

[Crystalyzer]

I don't believe without adding columns to your worksheet that this can be accomplished with a formula. I have written the VBA code below to allow the user to select the range to process and convert the string cell values to dates and times and then calculate the worksheet so that any formulas that use these cells will be recalculated and not show #VALUE! errors.

One caveat. I have NOT baked in any error handling so if a cell cannot be converted it might error out or replace the value with something unexpected. I am looking at this now, but you can test the below code on a copy of your workbook to prove with good data that it does what you are looking for.

Sub CvtStr2Dte()
    Dim c As Range, r As Range
    Dim dv As String, tv As String
    
    Set r = Application.InputBox("Select range to process.", "Convert Strings to Dates", , , , , , 8)
    
    For Each c In r
        dv = Mid(c, 8, 2) & "/" & Mid(c, 5, 2) & "/" & Mid(c, 11, 2)
        tv = Mid(c, 14, 8)
        c = DateValue(dv) + TimeValue(tv)
    Next
    Calculate
End Sub

 

[Crystalyzer]

Here is the code with error handling. Basically, if a cell value cannot be converted to date & time, it will be skipped and the original value will remain.

Sub CvtStr2Dte()
    Dim c As Range, r As Range
    Dim dv As String, tv As String
    Dim tmp1, tmp2
    Dim dteUpd As Boolean
    
    Set r = Application.InputBox("Select range to process.", "Convert Strings to Dates", , , , , , 8)
    
    If Not r Is Nothing Then
        On Error Resume Next
        dteUpd = False
            
        For Each c In r
            dv = Mid(c, 8, 2) & "/" & Mid(c, 5, 2) & "/" & Mid(c, 11, 2)
            tv = Mid(c, 14, 8)
            tmp1 = DateValue(dv)
            
            If Not IsDate(tmp1) Then
                dv = ""
                tv = ""
                tmp1 = Nothing
                GoTo NextC
            End If
            
            c = DateValue(dv) + TimeValue(tv)
            dteUpd = True
NextC:
        Next
                    
        If dteUpd Then Calculate
        
        On Error Resume Next
    End If
End Sub