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

标签：VBA,自定义函数

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

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

代码语言：javascript代码运行次数：0运行复制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中，则公式：

代码语言：javascript代码运行次数：0运行复制=xlDATEDIF(A1,A2,"y")返回两日期相差的年数。

代码语言：javascript代码运行次数：0运行复制=xlDATEDIF(A1,A2,"m")返回两日期相差的月份数。

代码语言：javascript代码运行次数：0运行复制=xlDATEDIF(A1,A2,"d")返回两日期相差的天数。

代码语言：javascript代码运行次数：0运行复制=xlDATEDIF(A1,A2,"ym")返回两日期相差的月份数，与年无关。

代码语言：javascript代码运行次数：0运行复制=xlDATEDIF(A1,A2,"yd")返回两日期相差的天数，与年无关。

代码语言：javascript代码运行次数：0运行复制=xlDATEDIF(A1,A2,"md")返回两日期相差的天数，与年月无关。