如何在Google Sheets中计算两日期间的浮点型月数？

单个单元格公式解决方案 #

假设起始日期在单元格A1，结束日期在B1，可以使用以下数组公式直接计算按月度占比的浮点月数：

=SUM(ARRAYFORMULA(
  DAYS(
    MIN(B1, EOMONTH(A1, SEQUENCE(DATEDIF(A1, B1, "M") + 1) - 1)),
    MAX(A1, EOMONTH(A1, SEQUENCE(DATEDIF(A1, B1, "M") + 1) - 2) + 1)
  ) / DAYS(
    EOMONTH(A1, SEQUENCE(DATEDIF(A1, B1, "M") + 1) - 1),
    EOMONTH(A1, SEQUENCE(DATEDIF(A1, B1, "M") + 1) - 2)
  )
))

公式原理

1. DATEDIF(A1, B1, "M") + 1：计算日期区间覆盖的总月份数（包含起始和结束月份）
2. SEQUENCE(...)：生成序列遍历每个覆盖的月份
3. 对每个月份：
   • MIN(B1, 月末日期)：确定该月的实际结束点（要么是月末，要么是给定的结束日期）
   • MAX(A1, 月初日期)：确定该月的实际起始点（要么是给定的起始日期，要么是月初）
   • 计算两点间的天数除以当月总天数，得到该月的占比
4. SUM：将所有月份的占比求和，得到最终的浮点月数

自定义函数替代方案 #

如果你觉得数组公式过于复杂，可以用Google Apps Script编写自定义函数，逻辑更直观：

1. 打开Google Sheets，点击「扩展程序」→「Apps 脚本」
2. 粘贴以下代码并保存：

function CALCULATE_MONTH_RATIO(startDate, endDate) {
  let totalMonths = 0;
  let currentDate = new Date(startDate);
  
  while (currentDate <= endDate) {
    // 获取当前月份的月末日期
    const monthEnd = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0);
    // 确定当前周期的结束点（月末或给定结束日期）
    const periodEnd = monthEnd <= endDate ? monthEnd : endDate;
    // 当月总天数
    const daysInMonth = monthEnd.getDate();
    // 当前周期内的天数
    const daysInPeriod = periodEnd.getDate() - currentDate.getDate() + 1;
    
    totalMonths += daysInPeriod / daysInMonth;
    
    // 跳到下一个月的第一天
    currentDate = new Date(monthEnd.getFullYear(), monthEnd.getMonth() + 1, 1);
  }
  
  return totalMonths;
}

3. 返回表格，在单元格中输入=CALCULATE_MONTH_RATIO(A1, B1)即可得到结果

两种方法对比 #

• 数组公式：无需额外授权，直接在单元格内运行，但公式较长，需要理解数组运算逻辑
• 自定义函数：代码逻辑清晰，便于后续修改扩展，但首次运行需要授权脚本访问权限

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