converting a strange date/time value into a recognisable date-time format

[plusunim]

Hi all. I have a column (say col A) that has values in the format yyyy-mm-ddThh_mm_ss. I decided to create the function below to convert them to dd/mm/yyyy hh:mm:ss in column B. However I'm getting a #Name? error. I'm not exactly a VBA proficient and I would like your opinion about where it is going wrong.

Function DateTime(dtval As Range) As Date

    'Converts the filenames with format yyyy-mm-ddThh_mm_ss into dd/mm/yyyy hh:mm:ss
    Dim dayfn, monthfn, yearfn, hourfn, minutefn, secondfn As String
    
    If dtval Is blank Then
        Exit Function
    Else
        yearfn = Left(dtval, 4)
        monthfn = Mid(dtval, 6, 2)
        dayfn = Mid(dtval, 9, 2)
        
        hourfn = Mid(dtval, 12, 2)
        minutefn = Mid(dtval, 15, 2)
        secondfn = Mid(dtval, 16, 2)
        
        DateTime = Format("dayfn/monthfn/yearfn hourfn:minutefn:secondfn", "dd/mm/yyyy hh:mm:ss")
    End If

End Function

Thanks!

 

[Rick Rothstein]

Assuming the upper case T is physically in the text, your function does not have to be as complex as you are assuming, this one-liner should work fine for you (of course, you will have to format the cell the function is in with the date/time display of your choice)...

Function DateTime(S As String) As Date
  DateTime = Replace(Replace(S, "T", " "), "_", ":")
End Function

 

[plusunim]

Assuming the upper case T is physically in the text, your function does not have to be as complex as you are assuming, this one-liner should work fine for you (of course, you will have to format the cell the function is in with the date/time display of your choice)...

Function DateTime(S As String) As Date
  DateTime = Replace(Replace(S, "T", " "), "_", ":")
End Function

Thanks for the reply. Yes, 'T' is in the filename to differentiate the date and time. The filename is generated by a commercial software and there is no way to go around it. I get your idea. I am using the following code but am still getting a #Name? error.

Function DateTime(S As String) As Date
  DateTime = Format(Replace(Replace(Replace(S, "T", " "), "_", ":"), "-", "/"), "dd/mm/yyyy hh:mm:ss")
End Function

Thanks.

 

[Rick Rothstein]

Thanks for the reply. Yes, 'T' is in the filename to differentiate the date and time. The filename is generated by a commercial software and there is no way to go around it. I get your idea. I am using the following code but am still getting a #Name? error.

Function DateTime(S As String) As Date
  DateTime = Format(Replace(Replace(Replace(S, "T", " "), "_", ":"), "-", "/"), "dd/mm/yyyy hh:mm:ss")
End Function

A UDF can only return a value to the cell it is in, it cannot do anything else (such as changing the format for the cell). Go back to my original UDF, put the formula using it in the cell, change the cells format, then you can copy that down (or across) as needed and the cell format will be placed in each cell it is copied to.

 

[plusunim]

[SOLVED] Re: converting a strange date/time value into a recognisable date-time format

A UDF can only return a value to the cell it is in, it cannot do anything else (such as changing the format for the cell). Go back to my original UDF, put the formula using it in the cell, change the cells format, then you can copy that down (or across) as needed and the cell format will be placed in each cell it is copied to.

Thanks! I was still getting a #Name? error until I changed that to Date_Time...

Works wonders!

Thanks!