VBA 转换和组合日期和时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21011427/
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 Convert and combine date and time
提问by blahblahblah
I am new to VBA and am working an a macro that will help me transform call records into something useful for analysis.
我是 VBA 新手，正在使用一个宏来帮助我将呼叫记录转换为对分析有用的内容。
Column E contains the Date of Call which is formatted YYYYMMDD. I need to convert to MM/DD/YYYY. (i.e. 20140101 convert to 1/1/2014)
E 列包含格式为 YYYYMMDD 的呼叫日期。我需要转换为 MM/DD/YYYY。（即 20140101 转换为 1/1/2014）
Column F contains the Time of Call which is formatted HHMMSS or HMMSS depending on whether the hour has two digits or one. I need to convert to HH:MM:SS (i.e. 130101 or 90101 which needs to convert to 13:01:01 and 9:01:01, respectively). Because the hour is missing the tens digit if the value is below ten, (below) I have added a "0" to the beginning of the value so I can use the date function.
F 列包含呼叫时间，其格式取决于小时是两位数还是一位数，其格式为 HHMMSS 或 HMMSS。我需要转换为 HH:MM:SS（即 130101 或 90101，需要分别转换为 13:01:01 和 9:01:01）。因为如果值低于十，小时将缺少十位数字，（下图）我在值的开头添加了一个“0”，以便我可以使用日期函数。
I currently enter the the following formula in Column K and autofill until the end of the range:
我目前在 K 列中输入以下公式并自动填充直到范围结束：
=DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2))+TIME(LEFT(IF(LEN(F2)=5, 0&F2, F2),2),MID(IF(LEN(F2)=5, 0&F2, F2),3,2),RIGHT(IF(LEN(F2)=5, 0&F2, F2),2))
=日期(左(E2,4),中(E2,5,2),右(E2,2))+时间(左(IF(LEN(F2)=5, 0&F2, F2),2),MID( IF(LEN(F2)=5, 0&F2, F2),3,2),RIGHT(IF(LEN(F2)=5, 0&F2, F2),2))
The formula results in a value like "1/1/2013 13:01:01".
该公式产生类似“1/1/2013 13:01:01”的值。
Can someone help me write the VBA code to automate this process?
有人可以帮我编写 VBA 代码来自动化这个过程吗？
Thank you.
谢谢你。
回答by Jerome Montino
Created separate UDFs for this. Paste the following into a module.
为此创建了单独的 UDF。将以下内容粘贴到模块中。
Function MorphDate(DateRng As Range)
Dim DateStr As String: DateStr = DateRng.Value
Dim Yr As String, Mt As String, Dy As String
Yr = Left(DateStr, 4)
Mt = Mid(DateStr, 5, 2)
Dy = Right(DateStr, 2)
MorphDate = Format(DateSerial(Yr, Mt, Dy), "m/dd/yyyy")
End Function
Function MorphTime(TimeRng As Range)
Dim TimeStr As String: TimeStr = TimeRng.Value
Dim Hh As String, Mm As String, Ss As String
If Len(TimeStr) = 5 Then TimeStr = "0" & TimeStr
Hh = Left(TimeStr, 2)
Mm = Mid(TimeStr, 3, 2)
Ss = Right(TimeStr, 2)
MorphTime = Format(TimeSerial(Hh, Mm, Ss), "hh:mm:ss")
End Function
Function MorphDateTime(DateRng As Range, TimeRng As Range)
Application.Volatile
MorphDateTime = CDate(MorphDate(DateRng)) + CDate(MorphTime(TimeRng))
End Function
Now you can use the formulas MorphDateto change the date, MorphTimeto change the time, and MorphDateTimefor a combination of both.
现在您可以使用公式MorphDate来更改日期、MorphTime更改时间以及MorphDateTime两者的组合。
Screenshot:
截屏：
Let us know if this helps.
如果这有帮助，请告诉我们。
EDIT:
编辑：
If you want to use it inside a subroutine, add the following code to the module:
如果要在子例程中使用它，请将以下代码添加到模块中：
Sub MorphingTime()
Dim DateRng As Range, Cell As Range
Set DateRng = Range("E2:E100") '--Modify as needed.
For Each Cell in DateRng
Range("K" & Cell.Row).Value = MorphDateTime(Cell, Cell.Offset(0,1))
Next Cell
End Sub
Hope this helps.
希望这可以帮助。
