如何动态计算多年期合同各年度涉及天数以进行收入确认？

Excel/Google Sheets 公式解法 #

对于任意合同的起始日期（假设在单元格A1）和结束日期（假设在单元格B1），计算指定年度Y的涉及天数，可使用以下公式：

=MAX(0, MIN(DATE(Y,12,31), B1) - MAX(DATE(Y,1,1), A1) + 1)

公式逻辑：

• MAX(DATE(Y,1,1), A1)：定位该年度内合同的实际起始点（若合同起始晚于年初，则取合同起始日；否则取1月1日）
• MIN(DATE(Y,12,31), B1)：定位该年度内合同的实际结束点（若合同结束早于年末，则取合同结束日；否则取12月31日）
• 两者差值加1得到包含起止日的天数，MAX(0,...)避免年度不在合同周期内时出现负数

如果需要批量生成所有涉及年度的天数：

1. 提取合同的起始年（=YEAR(A1)）和结束年（=YEAR(B1)）
2. 生成从起始年到结束年的年度序列
3. 对每个年度应用上述公式即可

Python 代码实现 #

通过datetime模块编写函数，自动遍历合同覆盖的所有年度并计算天数：

from datetime import datetime, date

def calculate_yearly_contract_days(start_date_str, end_date_str):
    # 解析输入的日期字符串为date对象
    start_date = datetime.strptime(start_date_str, "%Y-%m-%d").date()
    end_date = datetime.strptime(end_date_str, "%Y-%m-%d").date()
    
    yearly_days = {}
    current_year = start_date.year
    
    # 遍历合同覆盖的每一个年度
    while current_year <= end_date.year:
        year_start = date(current_year, 1, 1)
        year_end = date(current_year, 12, 31)
        
        # 确定该年度内的合同实际起止日期
        period_start = max(start_date, year_start)
        period_end = min(end_date, year_end)
        
        # 计算天数（加1是因为包含起止当天）
        days_in_year = (period_end - period_start).days + 1
        yearly_days[current_year] = days_in_year
        
        current_year += 1
    
    return yearly_days

# 示例调用
contract_days = calculate_yearly_contract_days("2023-04-01", "2026-03-31")
for year, days in contract_days.items():
    print(f"{year}年: {days}天")

运行结果会自动处理闰年（比如2024年返回366天），若需排除工作日可额外结合dateutil库的rrule模块过滤。

SQL 批量计算方案 #

以MySQL为例，通过递归CTE生成合同覆盖的年度序列，批量计算所有合同各年度的天数：

WITH RECURSIVE contract_years AS (
    SELECT 
        contract_id,
        YEAR(start_date) AS contract_year,
        start_date,
        end_date
    FROM contracts
    UNION ALL
    SELECT 
        contract_id,
        contract_year + 1,
        start_date,
        end_date
    FROM contract_years
    WHERE contract_year + 1 <= YEAR(end_date)
)
SELECT
    contract_id,
    contract_year AS year,
    GREATEST(0, DATEDIFF(
        LEAST(STR_TO_DATE(CONCAT(contract_year, '-12-31'), '%Y-%m-%d'), end_date),
        GREATEST(STR_TO_DATE(CONCAT(contract_year, '-01-01'), '%Y-%m-%d'), start_date)
    ) + 1) AS days_in_year
FROM contract_years
ORDER BY contract_id, contract_year;

该查询会自动关联合同表，输出每个合同在对应年度的涉及天数，适用于批量处理多份合同的场景。

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