Excel实现字符串日期相加

关于在Excel中实现两个时间字符串相加，比如4天21小时0分钟+1天3小时1分钟=6天0小时1分钟.

主要思路:

  • 将字符串日期转化为分钟
  • 将分钟相加
  • 将总分钟转化为时间字符串

具体实现:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Function SumDay(rng As Range)
    Dim mins As Integer
    mins = 0
    For Each r In rng
        mins = mins + ConvertDayToMin(r.Value)
    Next r
    SumDay = ConvertMinToDay(mins)
End Function
Function ConvertDayToMin(day As String)
    dd = Mid(day, 1, InStr(1, day, "天") - 1)
    hh = Mid(day, InStr(1, day, "天") + 1, InStr(1, day, "小时") - InStr(1, day, "天") - 1)
    mm = Mid(day, InStr(1, day, "小时") + 2, InStr(1, day, "分钟") - InStr(1, day, "小时") - 2)
    ConvertDayToMin = dd * 24 * 60 + hh * 60 + mm
End Function
Function ConvertMinToDay(mins As Integer)
    dd = mins \ (24 * 60)
    hh = (mins - dd * 24 * 60) \ 60
    mm = (mins - dd * 24 * 60 - hh * 60)
    ConvertMinToDay = dd & "天" & hh & "小时" & mm & "分钟"
End Function