Solved

Excel Macro Formating

Posted on 2014-11-24
8
132 Views
Last Modified: 2014-11-24
I have a macro that I created to format some data.  This data utilized the dd/mm/yyyy hh:mm format.  I tried just formatting these columns to the mm/dd/yyyy hh:mm format but that doesn't work.  So my work around was that I did a text to columns split separating the date and time fields.  This works but I have noticed that many times the various time columns will display an incorrect formatting i.e.,  1.39E-02 in one or several cells, but not all of them.  I can clean this up and reformat these columns but feel that defeats the purpose of creating and running the macro.  I have attached two files the first is once called MIDataclean up, this is the one with my script in it.  The second is called waslog MI Reports, this contains my data that I am trying to clean up.  

First off, I apologize for the uncleanliness of my script as I am still learning but any help or feedback you can provide  to help run this script more efficiently that would help.
MIDataCleanup.xlsm
waslog-MI-Report.xls
0
Comment
Question by:Rrave26
  • 5
  • 3
8 Comments
 

Author Comment

by:Rrave26
Comment Utility
Ok, it helps if I put in what I am looking for help on right?  So, I want to understand why the formatting of my columns that contains a 24 hour time value doesn't always format correctly.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
On first blush, your code at present does not work properly. I presume that your computer is in the US date format region.

If you had changed "19/11/2014" to "09/11/2014" (meaning in British date format, 9th date of November), your code would transform it into 9th month, 11 day - you will have date corruption.

Before going on more in this, can you confirm that your computer is set up to US date regional format, as the solution will be different depending on what it is set up to be.
0
 

Author Comment

by:Rrave26
Comment Utility
My computer is set up for the US Date regional format
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
To answer your question, the reason is your formatting of a 24 hour time value doesn't always format correctly is because of this line:

WorksheetFunction.Trim(Cell.Value)

It changes the formatting.

Change that line to:

        myStr = Cell.NumberFormat
        Cell.Value = WorksheetFunction.Trim(Cell.Value)
        Cell.NumberFormat = myStr
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Here's your updated code:

Sub MIDataCleanup()
'
' MIDataCleanup Macro
' Shortcut ctrl+i

Dim Cell As Range

    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Ticket Number"
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("S:S").Select
    Selection.Cut Destination:=Columns("Q:Q")
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("W:W").Select
    Selection.Cut Destination:=Columns("U:U")
    Columns("V:V").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Y:Y").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AA:AA").Select
    Selection.Cut Destination:=Columns("Y:Y")
    Columns("Z:Z").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.SmallScroll ToRight:=4
    Columns("AE:AE").Select
    Columns("AE:AE").Cut Destination:=Columns("A:A")
    Columns("A:A").Select
    Selection.ColumnWidth = 16
    Columns("AD:AD").Select
    Selection.Cut Destination:=Columns("C:C")
    Columns("C:C").Select
    Selection.ColumnWidth = 15.22
    Columns("AF:AF").Select
    Selection.Cut Destination:=Columns("D:D")
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Reconvene"
    Range("D2").Select
    Columns("D:D").ColumnWidth = 11.67
    Columns("E:E").ColumnWidth = 9.78
    Columns("AG:AH").Select
    Selection.Cut Destination:=Columns("F:G")
    Columns("F:G").Select
    Selection.ColumnWidth = 13.56
    Selection.ColumnWidth = 6.44
    Columns("AC:AC").Select
    Selection.Cut Destination:=Columns("H:H")
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Services Impacted"
    Range("I2:I17").Select
    ActiveWindow.SmallScroll Down:=-15
    Call SeparateDateAndTime
    Range("K2:K17").Select
    Call SeparateDateAndTime
    Range("M2:M17").Select
    Call SeparateDateAndTime
    Range("O2:O17").Select
    Call SeparateDateAndTime
   Range("Q2:Q17").Select
    Call SeparateDateAndTime
   Range("S2:S17").Select
    Call SeparateDateAndTime
    Range("U2:U17").Select
    Call SeparateDateAndTime
    Range("W2:W17").Select
    Call SeparateDateAndTime
    Range("Y2:Y17").Select
    Call SeparateDateAndTime
    Range("AA2:AA17").Select
    Call SeparateDateAndTime
    Columns("AC:AH").Select
    Selection.Delete Shift:=xlToLeft
    Range("i1").Select
    ActiveCell.FormulaR1C1 = "Start Date of Incident"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Outage Start Time"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "IMT Engaged Date"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "IMT Engaged Time"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "IMT Managed Start Date"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "IMT Managed Start Time"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "First Support Team Paged Date"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "First Support Team Paged Time"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Initial IR Sent Date"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Initial IR Sent Time"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Problem Solver Notified Date"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Problem Solver Notified Time"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "Succesful Health Check Date"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Succesful Health Check Time"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "Service Available Date"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Service Available Time"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "IMT Engagement End Date"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "IMT Engagement End Time"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "IMT Admin Tasks Completed Date"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "IMT Admin Tasks Completed Time"

For Each Cell In ActiveSheet.UsedRange.Columns("A:AB").Cells 'NOTNEEDED
    x = x + 1 'NOTNEEDED
        myStr = Cell.NumberFormat 'NOTNEEDED
        Cell.Value = WorksheetFunction.Trim(Cell.Value) 'NOTNEEDED
        Cell.NumberFormat = myStr 'NOTNEEDED
    Next 'NOTNEEDED

End Sub

Sub SeparateDateAndTime()
For Each Cell In Selection.Cells
        myStr = Cell.Value
        Cell.Value = Mid(myStr, 4, 3) & Left(myStr, 3) & Mid(myStr, 7, 4)
        Cells(Cell.Row, Cell.Column + 1) = Mid(myStr, 12, 5)
        Cell.NumberFormat = "mm/dd/yyyy"
        Cells(Cell.Row, Cell.Column + 1).NumberFormat = "hh:mm"
    Next
End Sub

Open in new window


I've highlighted a few lines as NOTNEEDED - you may want to test it to see if you can do without them - but you might want them for other reasons.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
As usual, test it with your existing output, make sure it works - but the answer to your principal question was two posts above.
0
 

Author Closing Comment

by:Rrave26
Comment Utility
That solved my issue.  Can you explain why that string WorksheetFunction.Trim(Cell.Value)
 changed the value and what does it change it to?
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
It forces the cell to re-evaluate itself, and in some cases changes it to a number, representing a fraction of a day.

So 00:30 would be re-evaluated to around 0.02.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now