vba Excel 中的日期格式为 MM-DD-YYYY 但 Excel 识别为 DD-MM-YYYY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17519519/
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
Dates in Excel formatted as MM-DD-YYYY but Excel recognised as DD-MM-YYYY
提问by humanitiesclinic
I have a date column in my spreadsheet that is not sorting properly because some dates are in MMDDYYYY HH:MIN format while others are in DDMMYYYY HH:MIN format, and Excel recognises all as MMDDYYYY HH:MIN. How do standardise? How do I have Excel recognise the dates as DDMMYYYY HH:MIN, and convert MMDDYYYY HH:MIN (which are currently seen as DDMMYYYY)?
我的电子表格中有一个日期列没有正确排序，因为某些日期采用 MMDDYYYY HH:MIN 格式，而其他日期采用 DDMMYYYY HH:MIN 格式，并且 Excel 将所有日期识别为 MMDDYYYY HH:MIN。如何标准化？我如何让 Excel 将日期识别为 DDMMYYYY HH:MIN，并转换为 MMDDYYYY HH:MIN（目前被视为 DDMMYYYY）？
I tried to convert date to text using TEXT() but now I am stuck. DATE() doesn't allow me to specify the format I am converting from.
我尝试使用 TEXT() 将日期转换为文本，但现在我被卡住了。DATE() 不允许我指定要转换的格式。
回答by varocarbas
As said in some of the comments above, the regional settings of the computer can affect the way in which Excel deals with dates and, some times, can provoke difficult-to-fix situations.
正如上面的一些评论所说，计算机的区域设置会影响 Excel 处理日期的方式，有时会引发难以修复的情况。
The first solution for your problem is changing the (date) format of the cells; as far as I understand that you will be affecting the cells via VBA, it is better to rely on VBA code to make sure that everything will be OK.
您的问题的第一个解决方案是更改单元格的（日期）格式；据我了解，您将通过 VBA 影响单元格，最好依靠 VBA 代码来确保一切正常。
You can do a simple test to see how the date-format-modifications behave: put some dates in column A and the same ones in column B (i.e., cell B1: "=A1", cell B2 "=A2", etc.) and execute the following code from VBA:
您可以做一个简单的测试来查看日期格式修改的行为：在 A 列中放置一些日期，在 B 列中放置相同的日期（即，单元格 B1：“=A1”、单元格 B2“=A2”等。 ) 并从 VBA 执行以下代码：
Range("A:A").NumberFormat = "mm-dd-yyyy hh:mm"
Range("B:B").NumberFormat = "dd-mm-yyyy hh:mm"
If there is no problem, you would see the months in different positions in both columns. In that case, you might rely on this kind of code to make sure that the date format is the one you want.
如果没有问题，您会在两列中看到不同位置的月份。在这种情况下，您可能会依赖这种代码来确保日期格式是您想要的。
If this test is not passed successfully, I recommend you to rely on text rather than on dates to avoid problems: set the format of all the columns to text (.NumberFormat = "@"in VBA), parse the given values (day, month, etc.), treat them as dates in your calculations and convert them back into text when writing to Excel (after that you might do some experiments and see if converting the given cell into date delivers the behaviour you want).
如果这个测试没有成功通过，我建议你依靠文本而不是日期来避免问题：将所有列的格式设置为文本（.NumberFormat = "@"在 VBA 中），解析给定的值（日、月等），在您的计算中将它们视为日期，并在写入 Excel 时将它们转换回文本（之后您可能会做一些实验，看看将给定的单元格转换为日期是否能提供您想要的行为）。
