Excel VBA datediff 一起计算月、日和月？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26378047/
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 datediff to Calculate Months, Days and Month together?
提问by De De De De
I'm trying to calculate the time elapsed with the total amount of months, Days and Hours together using Datediff function. Is it not possible?
我正在尝试使用 Datediff 函数计算总月数、天数和小时数所用的时间。不可能吗？
DateDiff("d hh", datein, Now)
What can I do?
我能做什么？
回答by
That's not possible as the interval parametercan only be a single string.
这是不可能的，因为interval 参数只能是单个字符串。
You will have to do a bit more work like get the difference in hoursand if it's above 24 convert the part before decimal separator into days
您将不得不做更多的工作，例如获取差异hours，如果它高于 24，则将小数点分隔符之前的部分转换为天数
Sub Main()
Dim d1 As Date
d1 = "15/10/2014 08:00:03"
Dim d2 As Date
d2 = Now
Dim hrsDiff As Long
hrsDiff = DateDiff("h", d1, d2)
MsgBox IIf(hrsDiff >= 24, _
hrsDiff \ 24 & " days " & hrsDiff Mod 24 & " hours", _
hrsDiff & " hours")
End Sub
回答by smackenzie
This is rough and ready, but is just directional. You could make a user defined function. This one returns 1:2:22:15 as a string (but you could return a custom class instance with variables for months, days, hours, minutes). It doesn't account for date2 being before date1 (not sure what happens then), nor does it account for date1 only being a partial day (assumes date1 is midnight).
这是粗略的和准备好的，但只是方向性的。您可以创建用户定义的函数。这个返回 1:2:22:15 作为字符串（但您可以返回一个自定义类实例，其中包含月、日、小时、分钟的变量）。它不考虑 date2 在 date1 之前（不知道会发生什么），也不考虑 date1 只是部分日期（假设 date1 是午夜）。
Function MyDateDiff(date1 As Date, date2 As Date) As String
Dim intMonths As Integer
Dim datStartOfLastMonth As Date
Dim datStartOfLastHour As Date
Dim datEndOfMonth As Date
Dim intDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
Dim strResult As String
' Strip of any time
datStartOfLastMonth = DateSerial(Year(date2), Month(date2), Day(date2))
' check the dates arent in the same month
If Not ((Month(date1) = Month(date2) And Year(date1) = Year(date2))) Then
' how many months are there
intMonths = DateDiff("m", date1, date2)
Debug.Print (intMonths)
' how many days difference are there
intDays = DateDiff("d", DateAdd("m", intMonths, date1), date2)
Debug.Print (intDays)
' how many hours difference are there
intHours = DateDiff("h", datStartOfLastMonth, date2)
Debug.Print (intHours)
' how many minutes different are there
datStartOfLastHour = datStartOfLastMonth + (DatePart("h", date2) / 24)
intMinutes = DateDiff("n", datStartOfLastHour, date2)
Debug.Print (intMinutes)
Else
' Dates are in the same month
intMonths = 0
Debug.Print (intMonths)
' how many days difference are there
intDays = DateDiff("d", date1, date2)
Debug.Print (intDays)
' how many hours difference are there
intHours = DateDiff("h", datStartOfLastMonth, date2)
Debug.Print (intHours)
' how many minutes different are there
datStartOfLastHour = datStartOfLastMonth + (DatePart("h", date2) / 24)
intMinutes = DateDiff("n", datStartOfLastHour, date2)
Debug.Print (intMinutes)
End If
strResult = intMonths & ":" & intDays & ":" & intHours & ":" & intMinutes
MyDateDiff = strResult
End Function
Testing this:
测试这个：
?MyDateDiff("01-SEP-2014", "03-Oct-2014 22:15:33")
?MyDateDiff("01-SEP-2014", "03-Oct-2014 22:15:33")
Gives:
给出：
1:2:22:15
1:2:22:15
i.e. 1 month, 2 days, 22 minutes and 15 seconds.
即1个月2天22分15秒。
Reverse testing this by adding the components back onto date1 gives:
通过将组件重新添加到 date1 进行反向测试给出：
?DateAdd("n",15,DateAdd("h",22,DateAdd("d",2,DateAdd("m",1,"01-SEP-2014"))))
?DateAdd("n",15,DateAdd("h",22,DateAdd("d",2,DateAdd("m",1,"01-SEP-2014"))))
= "03-Oct-2014 22:15:33"
= "2014 年 10 月 3 日 22:15:33"
If we try with 2 dates in the same month:
如果我们在同一个月尝试 2 个日期：
?MyDateDiff("01-SEP-2014", "03-SEP-2014 22:15:33")
?MyDateDiff("01-SEP-2014", "03-SEP-2014 22:15:33")
We get:
我们得到：
0:2:22:15
0:2:22:15
Reverse testing this:
反向测试这个：
?DateAdd("n",15,DateAdd("h",22,DateAdd("d",2,DateAdd("m",0,"01-SEP-2014"))))
?DateAdd("n",15,DateAdd("h",22,DateAdd("d",2,DateAdd("m",0,"01-SEP-2014"))))
Gives:
给出：
03/09/2014 22:15:00
03/09/2014 22:15:00
But you may want to account for dates being the wrong way round...and you may only want date1 to be counted as a partial date if it starts later in the day....as I say, just a thought.
但是您可能希望将日期计算为错误的方式……如果日期在当天晚些时候开始，您可能只希望将 date1 计为部分日期……正如我所说，只是一个想法。
Regards
问候
i
一世
回答by Harry S
This may give you some ideas to correct for days in month or leap year Feb
这可能会给你一些想法来纠正二月或闰年的天数
Private Sub CommandButton1_Click()
DoDateA
End Sub
Sub DoDateA()
Dim D1 As Date, D2 As Date, DC As Date, DS As Date
Dim CA: CA = Array("", "yyyy", "m", "d", "h", "n", "s", "s")
Dim Va%(7), Da(7) As Date, Ci%
D1 = Now + Rnd() * 420 ' vary the * factors for range of dates
D2 = Now + Rnd() * 156
If D1 > D2 Then
[b4] = "Larger"
Else
[b4] = " smaller"
DS = D1
D1 = D2
D2 = DS
End If
[d4] = D1
[e4] = D2
DC = D2
For Ci = 1 To 6
Va(Ci) = DateDiff(CA(Ci), DC, D1)
DC = DateAdd(CA(Ci), Va(Ci), DC)
Va(Ci + 1) = DateDiff(CA(Ci + 1), DC, D1)
If Va(Ci + 1) < 0 Then ' added too much
Va(Ci) = Va(Ci) - 1
DC = DateAdd(CA(Ci), -1, DC)
Cells(9, Ci + 3) = Va(Ci + 1)
Cells(8, Ci + 3) = Format(DC, "yyyy:mm:dd hh:mm:ss")
End If
Da(Ci) = DC
Cells(5, Ci + 3) = CA(Ci)
Cells(6, Ci + 3) = Va(Ci)
Cells(7, Ci + 3) = Format(Da(Ci), "yyyy:mm:dd hh:mm:ss")
Cells(10, Ci + 3) = DateDiff(CA(Ci), D2, D1)
Next Ci
End Sub
