Link to home
Start Free TrialLog in
Avatar of AckeemK
AckeemK

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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AckeemK
AckeemK

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?
Avatar of AckeemK

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-
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

Open in new window

Avatar of AckeemK

ASKER

Thank you, this worked perfectly.