Solved

Excel Macro Formating

Posted on 2014-11-24
8
134 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
ID: 40462241
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
ID: 40462264
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
ID: 40462272
My computer is set up for the US Date regional format
0
Gigs: Get Your Project Delivered by an Expert

Select from 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.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40462294
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40462318
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
ID: 40462356
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
ID: 40462382
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
ID: 40462390
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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