VBA 日期收集正确但输出错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12461239/
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
VBA dates collected correctly but output wrongly
提问by mezamorphic
I grab dates from one spreadsheet and output them onto another spreadsheet. After grabbing the date, when I debug.print it is in the correct format. When I output the date, debug.print also displays the correct format. However, the format on the spreadsheet the value has just been sent to, doesnt show the correct format.
我从一个电子表格中抓取日期并将它们输出到另一个电子表格中。获取日期后，当我 debug.print 它是正确的格式。当我输出日期时，debug.print 也会显示正确的格式。但是，值刚刚发送到的电子表格上的格式没有显示正确的格式。
I am using:
我在用：
Sheets(SheetName).Cells(RowNum, ColNum).Value = Data
Sheets(SheetName).Cells(RowNum, ColNum).NumberFormat = "dd/mm/yyyy"
afterI have pasted the value, but the months and days are still switched the wrong way.
在我粘贴了值之后，但月份和日期仍然以错误的方式切换。
Is there something I am doing wrong?? If I right click the cell it thinks it's date is dd/mm/yyyy but instead of 4th Sept it is showing 9th April.
有什么我做错了吗？如果我右键单击单元格，它认为它的日期是 dd/mm/yyyy，但它显示的是 4 月 9 日而不是 9 月 4 日。
采纳答案by mezamorphic
In the end I had to format the cell as "TEXT" to keep the correct format
最后我不得不将单元格格式化为“TEXT”以保持正确的格式
回答by Jook
This might be some trouble with localization:
这可能是本地化的一些麻烦：
Try using NumberFormatLocal, if DanielCooks tip didn't help ;)
NumberFormatLocal如果 DanielCooks 提示没有帮助，请尝试使用;)
edit:erlier it was statet by mister Cook, to check if the given data is correct.
编辑：更重要的是，库克先生声明了它，以检查给定的数据是否正确。
edit:
编辑：
With my german version I have quite some trouble to use /as the seperator, that is why i tryied with this code .NumberFormat ="dd-mm-yyyy;@"- works fine; I can switch days and month as I like.
对于我的德语版本，我在/用作分隔符时遇到了一些麻烦，这就是我尝试使用此代码的原因.NumberFormat ="dd-mm-yyyy;@"- 工作正常；我可以随意切换日期和月份。
edit:
编辑：
With .NumberFormatLocal = "TT/MM/JJJJ"I have to use the german shorts for day, month and year, but now I can use /as the seperator.
随着.NumberFormatLocal = "TT/MM/JJJJ"我必须使用的日，月和年德国的短裤，但现在我可以用/作为分隔符。
You should experiment a litte bit with some formats strings ;)
您应该尝试使用某些格式字符串；)
回答by Cerbera
Sorry to resurrect an old post, however I had a problem with VBA outputting a valid date as US style with the actual date changed for example 1st May changed to 5th Jan. I came upon this post but I didn't find the answer I needed here and now that I have worked it out I thought I would share it:
很抱歉恢复旧帖子，但是我在 VBA 输出有效日期时遇到了问题，因为实际日期更改为美国样式，例如 5 月 1 日更改为 1 月 5 日。我看到了这篇文章，但没有找到我需要的答案在这里，现在我已经解决了，我想我会分享它：
The key is not to define the variable storing the date as a "date" but as a "double", e.g.
关键不是将存储日期的变量定义为“日期”，而是定义为“双精度”，例如
Dim ReportDate As Double
ReportDate = Date
Range("E6").Value = ReportDate
This works as it outputs the numeric "date value" which excel then formats locally e.g. 41644 gets formatted as "05/01/14" using UK format or "01/05/14" using US format.
这是因为它输出数字“日期值”，然后在本地格式化，例如 41644 使用英国格式被格式化为“05/01/14”或使用美国格式被格式化为“01/05/14”。
Hope this proves useful to other people (probably me when I forget how I solved it in six months time).
希望这对其他人有用（可能是我忘记了我是如何在六个月内解决它的）。
回答by Joyce Cheung
(1) You need to define the variable to "Date" type to read the input, then set the date format before assigning it to the date variable.
(1) 您需要将变量定义为“Date”类型以读取输入，然后在将其分配给日期变量之前设置日期格式。
(2) You also need to format the date output to make it work !
(2) 您还需要格式化日期输出以使其正常工作！
'**Read the date input****
' **读取日期输入****
Dim date1 as Date
Range("B2").NumberFormatLocal = "dd/mm/yyyy"
date1 = Range("B2").Value
'**Output the date****
' **输出日期****
Range("E2").Value = date1
Range("E2").NumberFormatLocal = "dd/mm/yyyy"
