vba Excel VBA中的日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26035775/
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
Date Formatting in Excel VBA
提问by apkdsmith
I have written a long procedure in VBA to manipulate a data set. Part of this involves using and formatting dates, and I can't get it to work properly.
我在 VBA 中编写了一个很长的过程来操作数据集。其中一部分涉及使用和格式化日期，我无法让它正常工作。
The initial data as downloaded has dates in the format "yyyy-mm-ddThh:mm:ssZ" - e.g. 2014-12-11T04:59:00Z.
下载的初始数据的日期格式为“yyyy-mm-ddThh:mm:ssZ” - 例如 2014-12-11T04:59:00Z。
I then convert these into a date in UK format of "dd/mm/yyyy". Looping over all relevant cells, I use the following method:
然后我将这些转换为英国格式的“dd/mm/yyyy”日期。循环遍历所有相关单元格，我使用以下方法：
initial_date = Range("A1").Value
Dim publish_date As Date
publish_date = DateValue(Mid(initial_date,9,2) & "/" & Mid(initial_date,6,2) & "/" & Mid(initial_date,1,4))
Range("A1").Value = publish_date
This seems to work fine. The cell automatically changes format to "Date" and when I calculate the difference between these dates and dates in another column, it works fine.
这似乎工作正常。单元格自动将格式更改为“日期”，当我计算这些日期与另一列中的日期之间的差异时，它工作正常。
I then pick up these dates again and add to an array:
然后我再次选择这些日期并添加到数组中：
feed(1, 8) = Range("A1")
and then transfer this value into another array:
然后将此值传输到另一个数组中：
new_array(7, 1) = feed(1, 8)
Finally, I insert the value from the new array into a different cell. Having used UK date formatting thus far, I now want this value to display in the form "mm/dd/yyyy".
最后，我将新数组中的值插入到不同的单元格中。到目前为止使用英国日期格式，我现在希望这个值以“mm/dd/yyyy”的形式显示。
Range("E1") = new_array(7, 1)
Range("E1").NumberFormat = "mm/dd/yyyy"
This is where it goes wrong.
这就是出错的地方。
Running the code as above, the dates are all displayed as "dd/mm/yyyy" format. Excel changes the format of the cells to "custom". Finding the difference between these and other dates works, so the data seems to be stored correctly but not displaying as I want.
运行如上代码，日期全部显示为“dd/mm/yyyy”格式。Excel 将单元格的格式更改为“自定义”。找到这些日期和其他日期之间的差异是有效的，因此数据似乎正确存储但未按我想要的方式显示。
If I change the code to
如果我将代码更改为
Range("E1") = new_array(7, 1)
Range("E1").NumberFormat = "dd/mm/yyyy"
the dates still do not display correctly. Dates which can be written either way around (i.e. day is equal to or less than 12) display correctly, but are in fact the wrong date. i.e. 2014-12-11T04:59:00Z displays as 12/11/2014 which is what I want, but Excel thinks the date is the 12th November instead of 11th December. Dates such as 29/09/2014 which cannot be written both ways round display in UK format, but are not recognised properly by Excel which thinks the long date should be "29/09/2014" instead of "29 September 2014".
日期仍然不能正确显示。可以任意书写的日期（即日等于或小于 12）显示正确，但实际上是错误的日期。即 2014-12-11T04:59:00Z 显示为 12/11/2014，这是我想要的，但 Excel 认为日期是 11 月 12 日而不是 12 月 11 日。诸如 29/09/2014 之类的日期不能以英国格式显示，但不能被 Excel 正确识别，Excel 认为长日期应该是“29/09/2014”而不是“2014 年 9 月 29 日”。
When I remove the formatting line completely, the results are the same.
当我完全删除格式化行时，结果是一样的。
I'm sorry for the rather long-winded explanataion, but there's clearly something I'm not understanding about how Excel and VBA handle, store and format dates. If anyone could enlighten me what's going wrong, I'd really appreciate it!
对于冗长的解释，我很抱歉，但显然我不明白 Excel 和 VBA 如何处理、存储和格式化日期。如果有人能启发我出了什么问题，我将不胜感激！
(Note, in all the code snippets above, where I quote e.g. Range("A1")this is shorthand. There is in fact a lot more code involved in looping and selecting values, but I know this works, so I am not concerned. The extracts above just demonstrate what happens for the first value in each loop.)
（请注意，在上面的所有代码片段中，我引用的代码例如Range("A1")这是速记。实际上，循环和选择值涉及更多代码，但我知道这是有效的，所以我并不担心。上面的摘录只是演示每个循环中的第一个值会发生什么。）
采纳答案by apkdsmith
try
尝试
Range("E1").NumberFormat = "mm/dd/yyyy"
Range("E1") = Format(new_array(7, 1), "mm/dd/yyyy")
