Google Sheets是否有内置公式计算两日期间精确的分数年份？

问题背景 #

需要计算两个日期之间的精确分数年份，但常用方法无法返回准确结果：

• 将日期差除以固定天数（365、354或364.25），未考虑闰年差异
• 使用YEARFRAC函数，其各种日期计数规则均不符合「按起止年份实际天数占比+完整整年数」的精确计算逻辑

精确计算需分三部分：

1. 起始日期在其所在年份的占比（从起始日到当年年末）
2. 起止日期之间的完整整年数量
3. 结束日期在其所在年份的占比（从当年年初到结束日）

核心原因是起止年份可能为闰年，单日在年份中的占比会随当年总天数变化：

• 2020-02-16所在的2020年是闰年，全年366天（原提问中笔误写为365天）
• 2021-02-16所在的2021年非闰年，全年365天

示例验证 #

示例1

• start date = 2020-02-16
• end date = 2022-05-12
  现有方法结果均与精确值存在偏差，精确计算：
• 起始年份占比：319/366 ≈ 0.871584699
• 完整整年数：1
• 结束年份占比：131/365 ≈ 0.35890411
• 总计：≈ 2.230488809

示例2

• start date = 2021-02-16
• end date = 2023-05-12
  精确计算：
• 起始年份占比：318/365 ≈ 0.871232877
• 完整整年数：1
• 结束年份占比：131/365 ≈ 0.35890411
• 总计：≈ 2.230137

解决方案 #

Google Sheets没有直接实现该逻辑的内置公式，但可通过组合内置函数完成精确计算，无需创建命名公式。假设起始日期在单元格A1，结束日期在B1，使用以下公式：

=IF(A1>=B1, 0,
  (DATE(YEAR(A1), 12, 31) - A1 + 1) / IF(ISLEAP(YEAR(A1)), 366, 365)
  + MAX(YEAR(B1) - YEAR(A1) - 1, 0)
  + (B1 - DATE(YEAR(B1), 1, 1) + 1) / IF(ISLEAP(YEAR(B1)), 366, 365)
)

公式拆解

• 第一部分：计算起始日到当年年末的天数占起始年份总天数的比例（+1是为了包含起始日当天）
• 第二部分：计算起止年份之间的完整整年数，若起止年份相同则返回0
• 第三部分：计算当年年初到结束日的天数占结束年份总天数的比例
• 外层IF处理起始日期晚于结束日期的情况，返回0

内容的提问来源于stack exchange，提问作者IMTheNachoMan