Format column with date to specific format - Excel VBA

Question

I am writing an excel vba macro.

I have a large worksheet with over 10,000 rows. One of the columns has the date in this format: 10/28/13 06:57 with the Cells having a General Number for formatting.

I would like to format it to have only four digits in this format: mmdd (1028 for the example above)

Here is what I have so far in the subroutine:

' This subroutine formats the columns to what is necessary for output
Sub formatColumns()
    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Dim cell As Range

    For Each cell In Range("B3:B" & lastRow)
        cell.NumberFormat = "mmdd;@"
    Next
End Sub

If you can, make the conversion in memory instead of looping over 10 000 cells. If the format is fixed, you should be able to extract the required string from the input. — Trace
– Trace, Commented Dec 17, 2013 at 14:45
What is the problem? Are you trying to apply formatting or are you trying to convert a datetime value to a formatted string? — David Zemens
– David Zemens, Commented Dec 17, 2013 at 14:45
@DavidZemens - Currently, the celss have a General - Number format. I want to apply formatting to those values. — RXC
– RXC, Commented Dec 17, 2013 at 14:54

David Zemens · Accepted Answer · 2013-12-17 14:44:44Z

5

I don't think there's anything wrong with the code you posted, so I'm not quite sure what you're trying to do or where your current effort is failing, but you don't need a For/Next loop to do this, you can apply NumberFormat property to the entire range:

Sub formatColumns()
    Dim lastRow as Long
    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Range("B3:B" & lastRow).NumberFormat = "mmdd"

End Sub

answered Dec 17, 2013 at 14:44

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Zemens Over a year ago

@mehow hmmmm this numberformat works for me, Win 7/Excel 2010.

user2140173 Over a year ago

Win7/Excel 2010 - European locale it does not.

Our Man in Bananas Over a year ago

it works for me too, I'm pretty sure I'm on UK or European locale...if I type =TODAY() in a cell, I get 25/02/2014 then I can format it as ddmm

score 3 · Accepted Answer · 2013-12-18 08:21:31Z

3

You can't really format it that way using that mmdd number format. At least it doesn't work for me. (Excel 2010/ Win 7 European Locale)

What I'd suggest it's adding an extra column and copying over the data to keep the original format then hiding that column in the end. Meanwhile, you would use Month() and Day() functions to create the desired format.

Sub formatColumns()
    Dim lastRow As Long

    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Dim cell As Range
    For Each cell In Range("B3:B" & lastRow)
        'cell.NumberFormat = "mmdd;@"
        cell.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        cell.Offset(0, -1).NumberFormat = "@"
        cell.Offset(0, -1) = _
            IIf(Len(Month(cell)) = 1, "0" & Month(cell), Month(cell)) & _
            IIf(Len(Day(cell)) = 1, "0" & Day(cell), Day(cell))
    Next

    Columns("C:C").EntireColumn.Hidden = True
End Sub

if you don't want to add an extra column but you dont mind loosing the original format then you can just replace the contents of the cells using this code

Sub formatColumns()
    Dim lastRow As Long

    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Dim cell As Range
    For Each cell In Range("B3:B" & lastRow)
        'cell.NumberFormat = "mmdd;@"
        cell = IIf(Len(Month(cell)) = 1, "0" & Month(cell), Month(cell)) & _
        IIf(Len(Day(cell)) = 1, "0" & Day(cell), Day(cell))
    Next

End Sub

edited Dec 18, 2013 at 8:21

answered Dec 17, 2013 at 14:52

user2140173

4 Comments

RXC Over a year ago

Would having hidden columns in the spreadsheet affect anything when I try and save it in different formats?

user2140173 Over a year ago

@RXC well, that's a very broad question. It depends on the format. It would affect the structure in a CSV because there would be an extra column on each line. See updated answer

rheitzman Over a year ago

Leading zeros: Right("0" & Month("2/1/12"), 2) & Right("0" & Year("2/1/12"), 2)

Our Man in Bananas Over a year ago

+1, but I'd suggest just putting a formula in column D to give the mmdd then maybe copy it down all rows in one step to do all the work rather than thousands of operations?

Collectives™ on Stack Overflow

Format column with date to specific format - Excel VBA

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related