vba 如何解决混合日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21423262/
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
How to solve mixed date formats
提问by distante
I'm trying to write VBA code in Excel 2010 to make some time calculations. Everything is working as I want BUT for the cell date format. The Excel Sheets were created by merging several .xlsx files generated by different PCs and a Hardware Data-logger. The problem is that some sheets had the date as mm/dd/yy hh:mm:ss AM/PMand others dd/mm/yy hh:mm:ss AM/PM, with both mixed in one file.
我正在尝试在 Excel 2010 中编写 VBA 代码来进行一些时间计算。对于单元格日期格式，一切都按我想要的方式工作。Excel 表格是通过合并由不同 PC 和硬件数据记录器生成的多个 .xlsx 文件而创建的。问题是有些工作表的日期为 asmm/dd/yy hh:mm:ss AM/PM和其他工作表dd/mm/yy hh:mm:ss AM/PM，两者都混合在一个文件中。
I tried to change everything to Selection.NumberFormat = "dd/mm/yy hh:mm;@"but some cells just don't change. I also tried this function:
我试图改变一切，Selection.NumberFormat = "dd/mm/yy hh:mm;@"但有些细胞只是不改变。我也试过这个功能：
Function Arreglar_Fecha()
Dim temp As String
temp = ""
Do While ActiveCell.Value <> ""
temp = ActiveCell.Value
ActiveCell.Value = Day(temp) & "/" & Month(temp) & "/" & Year(temp) & " " & Hour(temp) & ":" & Minute(temp)
ActiveCell.Offset(1, 0).Select
Loop
End Function
But still, some cells changed, some did not. And what is worse, some get the day and month mixed!
但是，有些细胞改变了，有些没有。更糟糕的是，有些人将日期和月份混为一谈！
I have access to some of the original .xlsx files and in there also wasn't able to change all the date formats.
我可以访问一些原始的 .xlsx 文件，但也无法更改所有日期格式。
Anyone have any idea how I can fix this?
任何人都知道我该如何解决这个问题？
EDITHere I got permission for put an original Excel file Excel Data.
编辑在这里，我获得了放置原始 Excel 文件Excel Data 的许可。
回答by pnuts
You will have to trace back to your source data. There is no way Excel itself knows whether 1/2/2014 for example should be the first of February or the second of January, only that it is either 41671 or 41641.
您将不得不追溯到您的源数据。例如，Excel 本身无法知道 1/2/2014 应该是二月一日还是一月二日，只能知道它是 41671 还是 41641。
EditIn your second example, clearly 28/9/2013 17:59is September 28. If 10/01/13 12:11:00 PMhad the same formatting (perhaps came from the same file) then it is January 10. But if the formatting was different then it could be October 1. If you are seeing AMs and PMs with formatting as dd/mm/yy hh:mm;@then some of your data is text and there is no reliable 'automatic' way to switch this to a date/time serial number without knowing the text convention (ie whether DMY or MDY), hence the need to revert to source.
编辑在你的第二个例子中，显然28/9/2013 17:59是 9 月 28 日。如果10/01/13 12:11:00 PM格式相同（可能来自同一个文件），那么它是 1 月 10 日。但如果格式不同，那么它可能是 10 月 1 日。如果你看到 AM 和 PM使用格式化，dd/mm/yy hh:mm;@那么您的某些数据是文本，并且在不知道文本约定（即 DMY 还是 MDY）的情况下，没有可靠的“自动”方式将其切换为日期/时间序列号，因此需要恢复到源。
Obviously 'day' values greater than 12 are actually months but that does not help much when for less than 13 it depends upon the formatting.
显然，大于 12 的“天”值实际上是几个月，但是当小于 13 时，这并没有多大帮助，这取决于格式。
In addition, given your various sources, there is a risk that both the 1900 and the 1904 conventions might have been used and even possibly others also (your data logger might be on UNIX time, which starts in 1970).
此外，鉴于您的各种来源，存在可能已使用 1900 和 1904 约定甚至可能还使用其他约定的风险（您的数据记录器可能使用 UNIX 时间，从 1970 年开始）。
回答by Siddharth Rout
ActiveCell.Value = Day(temp) & "/" & Month(temp) & "/" & Year(temp) & " " & Hour(temp) & ":" & Minute(temp)
ActiveCell.Value = Day(temp) & "/" & Month(temp) & "/" & Year(temp) & " " & Hour(temp) & ":" & Minute(temp)
Maybe the code is reading it as text? Try this (UNTESTED)
也许代码正在将其作为文本阅读？试试这个（未经测试）
Sub Arreglar_Fecha()
Dim temp As String, Tmp As String
Dim D As String, M As String, Y As String
Dim H As String, Mn As String
Do While ActiveCell.Value <> ""
temp = Trim(ActiveCell.Value)
D = Trim(Split(temp, "/")(0))
M = Trim(Split(temp, "/")(1))
Tmp = Trim(Split(temp, "/")(2))
Y = Trim(Split(Tmp, " ")(0))
Tmp = Trim(Split(Tmp, " ")(1))
H = Trim(Split(Tmp, ":")(0))
Mn = Trim(Split(Tmp, ":")(1))
ActiveCell.Value = Format(DateSerial(Val(Y), Val(M), Val(D)) & _
" " & TimeSerial(Val(H), Val(Mn), 0), _
"dd/mm/yy hh:mm;@")
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Trying it with a single test scenario works. The below gives you 13/08/13 05:31
在单个测试场景中尝试它是有效的。下面给你13/08/13 05:31
Sub Test()
Dim temp As String, Tmp As String
Dim D As String, M As String, Y As String
Dim H As String, Mn As String
temp = "13/8/2013 5:31"
D = Trim(Split(temp, "/")(0))
M = Trim(Split(temp, "/")(1))
Tmp = Trim(Split(temp, "/")(2))
Y = Trim(Split(Tmp, " ")(0))
Tmp = Trim(Split(Tmp, " ")(1))
H = Trim(Split(Tmp, ":")(0))
Mn = Trim(Split(Tmp, ":")(1))
Debug.Print Format(DateSerial(Val(Y), Val(M), Val(D)) & _
" " & TimeSerial(Val(H), Val(Mn), 0), _
"dd/mm/yy hh:mm;@")
End Sub
