在 Excel VBA 中更改日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16746984/
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
Change Date format in Excel VBA
提问by Kiran
I have the Date in the log file. The Date in the log file looks something like this :
我在日志文件中有日期。日志文件中的日期如下所示：
Mon May 14 21:31:00 EDT 2012
2012 年 5 月 14 日星期一 21:31:00 EDT
I would like to convert this into the format "dd-MM-yyyy hh:mm:ss"
我想将其转换为格式“dd-MM-yyyy hh:mm:ss”
Dim DateVal1 As String
Dim Date1 As Date
DateVal1 = "Mon May 14 21:31:00 EDT 2012"
Date1 = Format(DateVal, "ddd mmm dd hh:mm:ss EDT yyyy")
Date1 = Format(Date1 , "dd-MM-yyyy hh:mm:ss")
The following code is not converting into the format that I was expecting. Any Idea, If I am missing something here.
以下代码未转换为我期望的格式。任何想法，如果我在这里遗漏了什么。
Thanks
谢谢
回答by woodykiddy
You might want to consider extracting datetime components from your custom datetime string first. For example, you can get day, month, year, etc. You could probably utilize string manipulation functions, like Mid, Replaceand so on.
您可能要考虑首先从自定义日期时间字符串中提取日期时间组件。例如，您可以获得日、月、年等。您可能会使用字符串操作函数，例如Mid，Replace等等。
If your custom string is consistent, then you can create your own function that does string manipulations and outputs the datetime value.
如果您的自定义字符串是一致的，那么您可以创建自己的函数来执行字符串操作并输出日期时间值。
Just a thought here. Hope it helps.
这里只是一个想法。希望能帮助到你。
回答by Kazimierz Jawor
If you are running English version of Office (and have English operating system) this function should give your results which you need:
如果您运行的是英文版 Office（并且有英文操作系统），此功能应该会提供您需要的结果：
Mon May 14 21:31:00 EDT 2012 >> 2012-05-14 21:31:00
2012 年 5 月 14 日星期一 21:31:00 EDT >> 2012-05-14 21:31:00
Function DateConversion(DateVal1 As String)
Dim DateParts As Variant
DateParts = Split(DateVal1, " ")
Dim i
For i = 1 To 12
If InStr(1, Left(MonthName(i), 3), DateParts(1), vbTextCompare) > 0 Then Exit For
Next i
DateConversion = CDate(DateSerial(DateParts(UBound(DateParts)), i, DateParts(2)) & " " & DateParts(3))
End Function
However, if you are using any other language it could require some additional changes referring to month names (MonthName function returns month names in your system language).
但是，如果您使用任何其他语言，则可能需要对月份名称进行一些额外的更改（MonthName 函数以您的系统语言返回月份名称）。
EDIT: Solution for situation of different languages month names
编辑：不同语言月份名称情况的解决方案
In this situation we need to create additional array with month names to be able to compare part of the original data string.
在这种情况下，我们需要使用月份名称创建额外的数组，以便能够比较原始数据字符串的一部分。
Function DateConversionEng(DateVal1 As String)
Dim DateParts As Variant
DateParts = Split(DateVal1, " ")
Dim Months As Variant
Months = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "Octover", "November", "December")
Dim i
For i = 1 To 12
If InStr(1, Months(i), DateParts(1), vbTextCompare) > 0 Then Exit For
Next i
DateConversionEng = CDate(DateSerial(DateParts(UBound(DateParts)), i + 1, DateParts(2)) & " " & DateParts(3))
End Function
