Question

求助excel vba 算时间差

在excel中有一开始时间和一结束时间 （如开始时间 2010-10-8 10：21：19 结束时间：2010-10-10 23：59：18 ）现有计算这中间的时间差，且每天23：00到次日8：00的时间不计算在内（从2010-10-10 22：00：00到2010-10-10 23：55：00，中间时间差为3600秒；从2010-10-10 22：00：00到2010-10-11 7：59：59中间时间差也为3600秒；从2010-10-10 22：00：00到2010-10-11 9：00：00中间时间差为7200秒；从2010-10-11 7：00：00到2010-10-11 9：00：00中间时间差也为3600秒）

请高手写个vba程序，现有大量数据要计算，头痛啊，请好心人帮帮我啦，先谢谢啦！！！

Answer 1

Sub Macro1()

Dim i As Integer
Dim Date_1(0 To 2) As Variant
Dim Time_1(0 To 2) As Variant
Dim Date_2(0 To 2) As Variant
Dim Time_2(0 To 2) As Variant

For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 1
Date_1(0) = Split(Split(ActiveSheet.Cells(i, 1).Value, " ")(0), "-")(0)
Time_1(0) = Split(Split(ActiveSheet.Cells(i, 1).Value, " ")(1), ":")(0)
Date_2(0) = Split(Split(ActiveSheet.Cells(i, 2).Value, " ")(0), "-")(0)
Time_2(0) = Split(Split(ActiveSheet.Cells(i, 2).Value, " ")(1), ":")(0)
Date_1(1) = Split(Split(ActiveSheet.Cells(i, 1).Value, " ")(0), "-")(1)
Time_1(1) = Split(Split(ActiveSheet.Cells(i, 1).Value, " ")(1), ":")(1)
Date_2(1) = Split(Split(ActiveSheet.Cells(i, 2).Value, " ")(0), "-")(1)
Time_2(1) = Split(Split(ActiveSheet.Cells(i, 2).Value, " ")(1), ":")(1)
Date_1(2) = Split(Split(ActiveSheet.Cells(i, 1).Value, " ")(0), "-")(2)
Time_1(2) = Split(Split(ActiveSheet.Cells(i, 1).Value, " ")(1), ":")(2)
Date_2(2) = Split(Split(ActiveSheet.Cells(i, 2).Value, " ")(0), "-")(2)
Time_2(2) = Split(Split(ActiveSheet.Cells(i, 2).Value, " ")(1), ":")(2)
ActiveSheet.Cells(i, 3).Value = (Date_1(0) - Date_2(0)) & "年" & (Date_1(1) - Date_2(1)) & "月" _
& (Date_1(2) - Date_2(2)) & "日" & _
((Time_1(0) - Time_2(0)) * 3600 + (Time_1(1) - Time_2(1)) * 60 + (Time_1(2) - Time_2(2))) _
& "秒"
Next
End Sub
A列输入开始时间 (从A1开始)格式最好是
（2010/10/5 23：59：18 是“/”
2010-10-10 23：59：18 是“-”）
B列输入结束时间 (从B1开始)
插入个按钮链接宏 (Macro1)
点击按钮C列将是所要结果
试试吧

Answer 2

很简单。
hi我吧