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
AckeemKAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
Sub macro()

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" & Cells.Rows.Count).End(xlUp))
            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
End Sub

Open in new window

Regards
0
 
AckeemKAuthor Commented:
In line 10, I am receiving a error with this highlighted saying "Subscript out of range". Any thoughts?
0
 
Rgonzo1971Commented:
Could you send dummy file with error?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
AckeemKAuthor Commented:
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-
0
 
Rgonzo1971Commented:
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

0
 
AckeemKAuthor Commented:
Thank you, this worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.