Excel 单元格中的 VBA 日期格式错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17086886/
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
Wrong VBA date format in Excel cell
提问by clattenburg cake
I'm trying to print out different date formats in my Exel sheet using VBA. However, I can't seem to output the "yyyy-mm-dd" format, or the "mmm dd, yyyy" formats. The cells don't seem to print out the correct formats. For instance:
我正在尝试使用 VBA 在我的 Exel 工作表中打印出不同的日期格式。但是，我似乎无法输出“yyyy-mm-dd”格式或“mmm dd，yyyy”格式。单元格似乎没有打印出正确的格式。例如：
'Declare new date variables
Dim LongDateFmt As String
Dim ShortDateFmt As String
Dim MediumDateFmt As String
Dim NewDateFmt As String
'Change the date to the new submission date
MediumDateFmt = Format(Date, "yyyy-mm-dd")
Range("A1").Value = MediumDateFmt
LongDateFmt = Format(Date, "Long Date")
Range("A2").Value = LongDateFmt
ShortDateFmt = Format(Date, "Short Date")
Range("A3").Value = ShortDateFmt
NewDateFmt = Format(Date, "MMMM dd, yyyy")
Range("A4").Value = NewDateFmt
A1 prints out 13/06/2013, A2 prints 13-Jun-13, A3 prints out 13/06/2013 and A4 prints 13-Jun-13 as well.
A1 打印 13/06/2013，A2 打印 13-Jun-13，A3 打印 13/06/2013 和 A4 打印 13-Jun-13。
Is this a settings problem or is my code wrong?
这是设置问题还是我的代码错误？
回答by JosieP
you ought to change the cell format and not use formatted strings
你应该改变单元格格式而不是使用格式化的字符串
with Range("A1")
.Value = Date
.Numberformat = "yyyy-mm-dd"
end with
for instance
例如
回答by paxdiablo
Formatdoesn't set the format of a cell to what you want, it simply gives you a string of a specified format. Inserting that string into a cell won't necessarily give you the results you want since the current formatting of the cell may have an impact.
Format不会将单元格的格式设置为您想要的格式，它只是为您提供一个指定格式的字符串。将该字符串插入单元格不一定会得到您想要的结果，因为单元格的当前格式可能会产生影响。
If you want to change formatting of the cell itself,you need to set its NumberFormatproperty, with something like:
如果要更改单元格本身的格式，则需要设置其NumberFormat属性，例如：
Range("A1").NumberFormat = "yyyy-mm-dd"
