?
Solved

Excel Macro Formating

Posted on 2014-11-24
8
Medium Priority
?
141 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

770 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