MACRO that will format dates

[johnj81]

Hi Excel Community,

Please help. I have a spreadsheet that is generated by Access into a Excel 2007 spreadsheet. I need to manipulate some of the data. On the spreadsheet I have a DOB column that needs be reformatted to a date format. I've been using the formula:

=date(left(j2,4),mid(j2,5,2),right(j2,2))

and that seems to work. I tried formatting it using the right click option but that failed, I keep getting "#####...". Anyways, work has picked up (Healthcare Reform) and I'm getting between 50 - 70 of these spreadsheets a day. Does anyone know a MACRO that I can run that will reformat the 8 digit number "YYYYMMDD" into a usable date format i.e "MM/DD/YYYY". Currently, adding columns, plugging in formulas, copy and pasting values, re-coping and pasting data, and deleting columns is just way to time consuming.

Any help would be much appreciated Thanks = )

 

[Akinrotimi]

Hi,

Kindly follow the steps below:

A.Paste your 8 digit number "YYYYMMDD" in column J
B.Ensure columns k to P are empty

Please keep a copy of the workbook to prevent possible data loss:
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Date Format()
' akinrotimi, 13/08/2011
http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2826485
 
 Application.ScreenUpdating = False
    Range("k2").Select
    ActiveCell.FormulaR1C1 = _
        "=MID(RC[-1],5,2)&""/""&LEFT(RC[-1],2)&""/""&RIGHT(RC[-1],4)"
    Range("k2").Select
    ActiveCell.FormulaR1C1 = _
        "=MID(RC[-1],5,2)&""/""&RIGHT(RC[-1],4)&""/""&LEFT(RC[-1],4)"
    Range("k2").Select
    ActiveCell.FormulaR1C1 = _
        "=MID(RC[-1],5,2)&""/""&RIGHT(RC[-1],2)&""/""&LEFT(RC[-1],4)"
    Range("k2").Select
    Selection.AutoFill Destination:=Range("k2:k1000")
    Range("k2:k1000").Select
    Columns("B:B").EntireColumn.AutoFit
    Application.CutCopyMode = False
    Range("l2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""dd/mm/yyyy"")"
    Range("l2").Select
    Selection.AutoFill Destination:=Range("l2:l1000")
    Range("l2:l1000").Select
    Selection.Copy
    Range("m2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("m2:m1000").Select
    Selection.NumberFormat = "dd/mm/yyyy"
    Selection.TextToColumns Destination:=Range("m2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
    Range("n2").Select
    Range("k1:l1000").Select
    ActiveCell.FormulaR1C1 = "DATE"
      Range("M2").Select
    Selection.TextToColumns Destination:=Range("M2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
    Columns("M:M").Select
    Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
 
    Columns("M:M").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    Selection.Copy
    Range("N1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-3],RC[-2],RC[-1])"
    Range("O2").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    Selection.AutoFill Destination:=Range("O2:O1000")
    Range("O2:O1000").Select
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-3],2)=LEFT(RC[-2],2),RC[-2],RC[-1])"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O1000")
    Range("O2:O1000").Select
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-4]"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P1000")
    Range("P2:P1000").Select
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-4]"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P1000")
    Range("P2:P1000").Select
    Columns("K:M").Select
    Columns("O:P").Select
    Range("P1").Activate
    Selection.ClearContents
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]=RC[-1]"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O1000")
    Range("O2:O1000").Select
    Range("K1").Select
    Columns("j:j").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("k:k").EntireColumn.AutoFit
    Range("j1").Select
    Columns("K:L").Select
    Range("K1").Select
    Selection.Cut Destination:=Range("M1")
    Columns("K:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:N").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    Application.ScreenUpdating = True
 
end sub

You can then run the Date Format Macro

Regards

Rotimi

 

[johnj81]

Brilliant! That worked PERFECTLY Akinrotimi! Thank you!

 

[Akinrotimi]

johnj81,

Supporting you is always a pleasure.

Cheers!

Rotimi

 

[Rick Rothstein]

I tried formatting it using the right click option but that failed, I keep getting "#####...".

Try widening the column... when a date doesn't fit in the column width provided for it, Excel displays a series of # signs.