Excel VBA 创建日期+时间值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40384624/
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
Excel VBA create a date+time value
提问by jacek_wi
In my table I have two columns containing data of type: yyyymmddand hhmmddxx- first contains date and the other time with precision to 0.01 seconds. So for example I have 20161102and 17052349.
在我的表中，我有两列包含以下类型的数据：yyyymmdd和hhmmddxx- 第一个包含日期，另一个时间精度为 0.01 秒。例如，我有20161102和17052349。
I want to convert this into a single value showing a moment in time and display this in format dd/mm/yyyy hh:mm:ss.xx, so for example I want to get 02/11/2016 17:05:23.49. I think I need to use DateSerialand TimeSerialfunctions, but there are two problems I have no idea how to go around:
我想将其转换为显示某个时刻的单个值并以格式显示dd/mm/yyyy hh:mm:ss.xx，例如，我想将02/11/2016 17:05:23.49. 我想我需要使用DateSerial和TimeSerial函数，但是有两个问题我不知道如何解决：
- TimeSerial accepts hour, minute and second as argument, all are Integer type, how to pass fractions of a second to it?
- DateSerial will create my day and TimeSerial will create my time, is there a function that takes date and time as argument and creates a single "that day, on that time" value out of this?
- TimeSerial 接受小时、分钟和秒作为参数，都是整数类型，如何将秒的分数传递给它？
- DateSerial 将创建我的日期，TimeSerial 将创建我的时间，是否有一个函数将日期和时间作为参数并从中创建一个“那天，那个时间”的值？
回答by Nathan_Sav
Something like this, maybe??
像这样的，也许？？
? generate_timestamp("20161102","170522349")
? generate_timestamp("20161102","170522349")
gives
给
02/11/2016 17:05:23.49
02/11/2016 17:05:23.49
Function GENERATE_TIMESTAMP(strDate As String, strTime As String) As String
Dim dtDate As Date
Dim dtTime As Date
dtDate = DateSerial(Mid(strDate, 1, 4), Mid(strDate, 5, 2), Mid(strDate, 8, 2))
dtTime = TimeSerial(Mid(strTime, 1, 2), Mid(strTime, 3, 2), Mid(strTime, 6, 2))
GENERATE_TIMESTAMP = FormatDateTime(dtDate, vbShortDate) & " " & _
FormatDateTime(dtTime, vbLongTime) & "." & _
Mid(strTime, 8, 2)
End Function
回答by nbayly
EDIT: After testing found formatting was dropping the fractions of seconds, and just repeating fractions. I devised this formula to resolve. Note that the formula here does not output a serial representation of the datetime but is just a text string.
编辑：经过测试发现格式化会降低几分之一秒，而只是重复分数。我设计了这个公式来解决。请注意，此处的公式不会输出日期时间的串行表示，而只是一个文本字符串。
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))+TIME(LEFT(B1,2),MID(B1,3,2),MID(B1,5,2)),"m/d/yyyy hh:mm:ss")&"."&RIGHT(B1,2)
PREVIOUS RESPONSE:
以前的回应：
You can create the serial using the following formula:
您可以使用以下公式创建序列：
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))+TIME(LEFT(B1,2),MID(B1,3,2),MID(B1,5,2))+(RIGHT(B1,2)/(24*60*60*100))
And then format your cell as Custom with m/d/yyyy hh:mm:ss.ss. Here I am not passing the fraction of seconds to the TIME()function but rather adding as a fraction of a day.
然后使用 m/d/yyyy hh:mm:ss.ss 将您的单元格格式化为自定义。在这里，我不是将几分之一秒传递给TIME()函数，而是将其作为一天的几分之一进行添加。
