Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

Excel Macro Formating

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
Rrave26
Asked:
Rrave26
  • 5
  • 3
1 Solution
 
Rrave26Author Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Rrave26Author Commented:
My computer is set up for the US Date regional format
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
As usual, test it with your existing output, make sure it works - but the answer to your principal question was two posts above.
0
 
Rrave26Author Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now