Date/Time Format

[Aviles]

Hello, I've searched but I can't find guidance on the specific date and time format issue I have.

I have a spreadsheet with cells in range A2:A100 showing the following date and time format: Tue Jul 17 18:55:29 AEST 2018

I would like a macro to convert this to format: DD/MM/YYYY HH:MM:SS PM

Can someone help me with this please?

Thanks.

 

[lrobbo314]

This custom function should do it.

Function ToDate(s As String) As String
Dim SP() As String


SP = Split(s, " ")
ToDate = Format(SP(1) & " " & SP(2) & " " & SP(3), "DD/MM/YY hh:mm:ss AM/PM")
End Function

Then in B2, you would put =Todate(A1) then copy the formula down.

 

[Aviles]

This custom function should do it.

Function ToDate(s As String) As String
Dim SP() As String


SP = Split(s, " ")
ToDate = Format(SP(1) & " " & SP(2) & " " & SP(3), "DD/MM/YY hh:mm:ss AM/PM")
End Function

Then in B2, you would put =Todate(A1) then copy the formula down.

@lrobbo314, thank you for your help! That worked perfectly.

Although I forgot to add one step to the calculation... In addition to changing the date/time format, I also need to amend the time to GMT. AEST is 10 hours ahead of GMT, so I need to subtract 10 hours from the time in column A. I tried using + TimeSerial(10, 0, 0) at the end of the code but it did not work. Are you able to help me with this please?

Thanks again.

 

[lrobbo314]

Let me know if this works for you.

Function ToDate(s As String) As String
Dim SP() As String
Dim DT As Date


SP = Split(s, " ")
DT = (SP(1) & " " & SP(2) & " " & SP(3))
DT = DT - (10 / 24)
ToDate = Format(DT, "DD/MM/YY hh:mm:ss AM/PM")
End Function

 

[lrobbo314]

Or, even better....

Function ToDate(s As String) As Date
Dim SP() As String


SP = Split(s, " ")
ToDate = CDate((SP(1) & " " & SP(2) & ", " & SP(5) & " " & SP(3))) - (10 / 24)

End Function

And then just format your cells in the date format you like. This way the UDF is returning an actual date that you can perform date formulas, like adding and subtracting time, on. The previous versions were returning a string that would first need to be converted to a datetime value before adding or subtracting time to it.

 

[Aviles]

Or, even better....

And then just format your cells in the date format you like. This way the UDF is returning an actual date that you can perform date formulas, like adding and subtracting time, on. The previous versions were returning a string that would first need to be converted to a datetime value before adding or subtracting time to it.

Hi lrobbo, apologies for the late reply as I haven't had a chance to look at this until now. I haven't thoroughly tested yet it but it seems to work really well.
Thanks again for your help, I really appreciate it.

 

[Rick Rothstein]

Or, even better....

Function ToDate(s As String) As Date
Dim SP() As String


SP = Split(s, " ")
ToDate = CDate((SP(1) & " " & SP(2) & ", " & SP(5) & " " & SP(3))) - (10 / 24)

End Function

And then just format your cells in the date format you like. This way the UDF is returning an actual date that you can perform date formulas, like adding and subtracting time, on. The previous versions were returning a string that would first need to be converted to a datetime value before adding or subtracting time to it.

Just wanted to see if I could write this function as a one-liner without repeatedly Split'ting the date text string... here is what I came up with.

[table="width: 500"]
[tr]
	[td]Function ToDate(s As String) As Date
  ToDate = CDate(Evaluate("REPLACE(""" & Mid(s, 5, 15) & """," & 7 + (s Like "* # *") & ",0,"", ""&RIGHT(A1,4))")) - 10 / 24
End Function[/td]
[/tr]
[/table]