vba 如何在Excel VBA中减去时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43932066/
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 subtract Time in Excel VBA?
提问by bigbryan
How do I subtract Time in Excel VBA? I tried to subtract these two values, but I'm getting this value "2.1527777777778E-02" instead. What does this mean?
如何在 Excel VBA 中减去时间？我试图减去这两个值，但我得到了这个值“2.1527777777778E-02”。这是什么意思？
Timein = 12/7/16 12:00:00 AM
时间 = 12/7/16 12:00:00 AM
Timeout = 12/7/16 12:30:00 AM
超时 = 12/7/16 12:30:00 AM
Here's a sample of my code. Thanks in advance.
这是我的代码示例。提前致谢。
Dim Total as Double
Dim Timein as Date
Dim Timeout as Date
Total = TimeValue(Timeout) - TimeValue(Timein)
'Result Total=2.1527777777778E-02
回答by Holger
You can use the DateDiffFunction to get the difference in year/days/seconds or whatever.
您可以使用DateDiff函数来获取年/天/秒或其他单位的差异。
Here the example for minutes.
这里以分钟为例。
Dim Timein As Date
Dim Timeout As Date
Timein = "12/7/16 12:00:00 AM"
Timeout = "12/7/16 12:30:00 AM"
Debug.Print DateDiff("n", Timein, Timeout)
Output:
输出：
30
Interval Explanation
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
间隔说明
yyyy 年
季度
月
y 一年中的某一天
天
w 工作日
周
小时
分钟
秒
回答by CLR
2.1527777777778E-02is using a method of displaying a value called Scientific Notation. In your case, it means 2.1527777777778times 10to the power of -2. Or you could think of the E-02part as meaning shift the decimal point two places to the left (left because it's negative).
So: 0.021527777777778.
2.1527777777778E-02正在使用一种显示值的方法，称为科学记数法。根据你的情况，这意味着2.1527777777778次10给力-2。或者您可以将这E-02部分视为将小数点向左移动两位（向左，因为它是负数）。所以：0.021527777777778。
Excel treats time (both dates and times of day) as whole days so half a day (12 hours) would be represented as 0.5.
Excel 将时间（日期和时间）视为整天，因此半天（12 小时）将表示为0.5.
So the result itself represents the decimal fraction of an entire day, if you want this in minutes for example, you would multiply the value by 1440(24hr x 60min make a day) which would give you 31mins.
因此，结果本身表示一整天的小数部分，例如，如果您想要以分钟为单位，您可以将该值乘以1440（24 小时 x 60 分钟，一天），这将为您提供31分钟。
In your example, you're finding the difference between 12:00and 12:30so you should actually be getting a result of 2.08333333333333E-02which if multiplied by 1440would give you 30mins.
在你的例子中，你发现了12:00和之间的差异，12:30所以你实际上应该得到一个结果2.08333333333333E-02，如果乘以它1440会给你30分钟。
Excel gives you tools to find the difference between two points in time though that take all that complex math(s) away - DateDiffand @holger has given you everything you need there to write your own code.
Excel 为您提供了找到两个时间点之间差异的工具，尽管这可以消除所有复杂的数学运算 -DateDiff而 @holger 为您提供了编写自己的代码所需的一切。
回答by YowE3K
What you have done is perfectly correct, as can be seen by the following code:
您所做的完全正确，如以下代码所示：
Sub test()
Dim Total As Double
Dim Timein As Date
Dim Timeout As Date
Timein = CDate(Range("A1").Value)
Timeout = CDate(Range("A2").Value)
Total = TimeValue(Timeout) - TimeValue(Timein)
Debug.Print Total
Debug.Print Format(Total, "hh:mm:ss")
Range("A3").NumberFormat = "hh:mm:ss"
Range("A3").Value = Total
Debug.Print "Number of hours = " & Total * 24
End Sub
As mentioned by vacip in a comment, Date/Time variables are stored in VBA in "days" so, for example, Nowfor me is the number 42867.7513310185.
正如 vacip 在评论中提到的，日期/时间变量以“天”存储在 VBA 中，例如，Now对我来说是 number 42867.7513310185。
回答by Ben
You have defined the total as double, which returns you number. you need to dim the total as date so that returns you a time.
您已将总数定义为双倍，这将返回您的数字。您需要将总数调暗为日期，以便为您返回时间。
Dim Total as Date
Dim Timein as Date
Dim Timeout as Date
Total = TimeValue(Timeout) - TimeValue(Timein)
