一个代替DATEDIF函数的自定义函数

fanjy

发布于 2021-12-29 15:38:28

1.2K0

发布于 2021-12-29 15:38:28

文章被收录于专栏：完美Excel完美Excel

标签：VBA,自定义函数

大家知道，DATEDIF函数虽然可用，但已不在Excel插入函数可搜索的范围内了。

下面是在excelfox.com看到的一个自定义函数，虽然还存在Bug，但已经很不错了。

Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date,Interval As String) As Variant
    Dim NumOfYears As Long
    Dim NumOfMonths As Long
    Dim NumOfWeeks As Long
    Dim NumOfDays As Long
    Dim DaysDiff As Long
    Dim ydDaysDiff As Long
    Dim TSerial1 As Double
    Dim TSerial2 As Double
    If StartDate > EndDate Then
        Err.Raise 5
        Exit Function
    End If
    If InStr(1, "Y M D",Interval, vbTextCompare) Then
        Select Case UCase(Interval)
            Case "Y":xlDATEDIF = DateDiff("yyyy", StartDate, EndDate)
            Case "M":xlDATEDIF = DateDiff("m", StartDate, EndDate) + 1
            Case "D":xlDATEDIF = EndDate - StartDate
        End Select
    Else
        NumOfYears =DateDiff("yyyy", StartDate, EndDate)
        DaysDiff = EndDate -StartDate
        TSerial1 =TimeSerial(Hour(StartDate), Minute(StartDate), Second(StartDate))
        TSerial2 =TimeSerial(Hour(EndDate), Minute(EndDate), Second(EndDate))
        If 24 * (TSerial2 -TSerial1) < 0 Then EndDate = DateAdd("d", -1, EndDate)
        StartDate =DateSerial(Year(EndDate), Month(StartDate), Day(StartDate))
        If StartDate > EndDateThen
            StartDate =DateAdd("yyyy", -1, StartDate)
            NumOfYears = NumOfYears- 1
        End If
        ydDaysDiff = EndDate -StartDate
        NumOfMonths =DateDiff("m", StartDate, EndDate) + 1
        StartDate =DateSerial(Year(EndDate), Month(EndDate), Day(StartDate))
        If StartDate > EndDateThen
            StartDate =DateAdd("m", -1, StartDate)
            NumOfMonths = NumOfMonths- 1
        End If
        NumOfDays =Abs(DateDiff("d", StartDate, EndDate))
        Select Case UCase(Interval)
            Case "YM":xlDATEDIF = NumOfMonths
            Case "YD":xlDATEDIF = ydDaysDiff
            Case "MD":xlDATEDIF = NumOfDays
            Case Else
        End Select
    End If
End Function

使用方法：

假设日期在单元格A1和A2中，则公式：