将日期格式化为特定格式的列 - Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20637039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format column with date to specific format - Excel VBA
提问by RXC
I am writing an excel vba macro.
我正在编写一个 excel vba 宏。
I have a large worksheet with over 10,000 rows.
One of the columns has the date in this format: 10/28/13 06:57with the Cells having a General Number for formatting.
我有一个超过 10,000 行的大型工作表。其中一列具有以下格式的日期：10/28/13 06:57单元格具有用于格式化的通用编号。
I would like to format it to have only four digits in this format: mmdd(1028 for the example above)
我想将其格式化为只有四位数字：（mmdd上例为 1028）
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
回答by David Zemens
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/Nextloop to do this, you can apply NumberFormatproperty to the entire range:
我认为您发布的代码没有任何问题，所以我不太确定您要做什么或您当前的努力失败的地方，但是您不需要For/Next循环来执行此操作，您可以将NumberFormat属性应用于整个范围：
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
回答by David Zemens
You can't really format it that way using that mmddnumber format. At least it doesn't work for me. (Excel 2010/ Win 7 EuropeanLocale)
你不能真正使用那种mmdd数字格式来格式化它。至少它对我不起作用。（Excel 2010/Win 7欧洲语言环境）
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.
我建议它添加一个额外的列并复制数据以保持原始格式，然后最后隐藏该列。同时，您将使用Month()和Day()函数来创建所需的格式。
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 formatthen 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
