一直以来，我们都是用DATEDIF来计算2个日期的间隔，用在各种和日期有关的计算上面，但是实际上这个函数有一些隐藏bug，平时是看不出来，不过如果数据量很大，或者运气差碰到了一些特殊情况，就会出错，比如下面的数据红色部分，就是计算错误的地方，常见于从月尾开始到月初结束，以及闰年的2月计算，看下表，后面是正确结果，这里是按(结束-开始+1)来计算间隔天数=DATEDIF(A2,B2+1,"Y")&"年"&DATEDIF(A2,B2+1,"YM")&"月"&DATEDIF(A2,B2+1,"MD")&"天" 这些情况用一般的修正函数也挺麻烦，因为不知道DATEDIF的内部计算规则，也不知道还会有哪些错误，所以我干脆按我们计算间隔的完整规则写了一个VBA函数，当然这个函数是用VBA代码助手帮我写的，方法就是直接从源数据往前推算月份+天数，这个方式不使用减法计算，不用自己处理30还是31天的规则，就和人工数年月日一样的，最笨的方案，但肯定是最正确的计算方式，当然如果测试有什么地方不对可以评论区告诉我，继续修正' 计算两个日期之间的精确间隔（年月日）' 采用开始日期+N月的试探法，先确定月份间隔，再计算剩余天数Function DateDiff精确(ByVal startDate As Date, ByVal endDate As Date) As String ' 初始化变量 Dim totalMonths As Long Dim monthsFound As Boolean Dim i As Long, totalDay ' 首先用EOMONTH试探第0个月的月底是否大于等于结束日期 月底 = CDate(WorksheetFunction.EoMonth(startDate, 0)) If 月底 >= endDate Then If Day(startDate) = 1 And 月底 = endDate Then '月初到月底按1个月 totalMonths = 1 Else totalMonths = 0 totalDay = Day(endDate) '天数 End If monthsFound = True Else ' 如果月底小于结束日期，则进入EDATE试探阶段 monthsFound = False For i = 1 To 1200 ' 设置一个合理的上限 ' 使用EDATE试探（当月日期+i个月-1天 是否大于等于结束日期） 下个月日期 = CDate(WorksheetFunction.EDate(startDate, i) - 1) If 下个月日期 > endDate Then totalMonths = i - 1 monthsFound = True Exit For End If Next i
' 如果循环结束仍未找到，设置一个最大值 If Not monthsFound Then totalMonths = 1200 End If End If
' 计算年份和月份部分 Dim years As Long Dim months As Long
years = totalMonths \ 12 ' 整除得到年数 months = totalMonths Mod 12 ' 取余得到月数
' 计算剩余天数 Dim monthEndDate As Date Dim daysLeft As Long
If totalMonths = 0 Then ' 如果月份为0，直接计算天数差 daysLeft = endDate - startDate + 1 Else ' 使用EDATE计算月份后的日期再减1天 monthEndDate = WorksheetFunction.EDate(startDate, totalMonths) - 1
' 计算剩余天数 If monthEndDate >= endDate Then daysLeft = 0 Else daysLeft = endDate - monthEndDate End If End If
' 格式化结果 result = years & "年" & months & "月" & daysLeft & "天"
DateDiff精确 = resultEnd Function
这个就是E列的VBA自定义函数，不过这个需要启用vba支持如果要直接使用，那就要用下面的Lambda函数，这时候就是借助AI了，把VBA代码扔给AI让他转为Lambda函数，会得到一个无比复杂的Lambda函数 不过不用害怕 我们只要会套用就可以了!LAMBDA(x,y, LET( z, EOMONTH(x, 0), a, AND(DAY(x) = 1, z = y), b, IF( z >= y, IF(a, 1, 0), LET( c, 1200, d, SEQUENCE(c, 1, 1, 1), e, EDATE(x, d) - 1, f, MATCH(TRUE, e > y, 0), IF( ISNUMBER(f), f - 1, c ) ) ), g, QUOTIENT(b, 12), h, MOD(b, 12), i, IF(b = 0, x, EDATE(x, b) - 1), j, IF(b = 0, y - x+1, MAX(0, y - i)), g & "年" & h & "月" & j & "天" )) 这个lambda函数最简单的用法， 就是在名称管理器自定义名称引用位置写lambda函数代码确定后， 在表格里就可以直接使用了这个函数在WPS最新版里也可以正常使用，在lambda函数的时代，原则是任意复杂的计算都可以做到了，不过注意，一般先写出VBA函数，再让AI转为Lambda函数成功率比较高，这个级别的Lambda函数就建议完全不要手写了，也不必尝试理解，会用即可！
|
