asked on
Macro to format time within a report
I need a macro to format dates in two columns to read as such "3/8/15 11:58". I'm looking to format the "Date Created" column as well as the "Last Updated" column. I would like for this to occur in the 'Import' tab (these columns are H and I).
Report.xlsx
Report.xlsx
VB ScriptMicrosoft ExcelVisual Basic Classic
Last Comment
AckeemK
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
In line 10, I am receiving a error with this highlighted saying "Subscript out of range". Any thoughts?
Could you send dummy file with error?
ASKER
Within the report, when I try to import the file by pressing "Import Report File(s)" on the 'Table of Contents' tab, I receive that error. I attached the report and a file to import to test it and see the error I am getting.
DSE-Carelog-Report.xlsm
emc-ticket-history-view-2015-03-31-1432-
DSE-Carelog-Report.xlsm
emc-ticket-history-view-2015-03-31-1432-
then try
Sub FormatTime()
For Each sh In ActiveWorkbook.Sheets
sh.Activate
If sh.Name = "Current" Or sh.Name = "Import" Then
For Each c In Range(Range("H2"), Range("I" & Application.Max(2, Range("I" & Cells.Rows.Count).End(xlUp).Row)))
Debug.Print c.Address
If Not IsDate(c) And c <> "" Then
arrData = Split(c.Text, "-")
arrTime = Split(Right(arrData(2), 8), ":")
theDate = DateSerial(arrData(0), arrData(1), Left(arrData(2), 2))
theTime = TimeSerial(arrTime(0), arrTime(1), arrTime(2))
theDateTime = theDate + theTime
c.Value = theDateTime
End If
c.NumberFormat = "mm\/dd\/yyyy hh:mm"
Next
End If
Next
compare
End Sub
ASKER
Thank you, this worked perfectly.