如何编写计算财季内在职天数的Excel公式？

核心逻辑 #

我们需要灵活处理员工离职日期和目标财季起止的重叠关系，核心思路是先锁定实际的在职时间段：

• 员工的实际在职结束日期：取「离职日期」和「财季结束日期」的较小值（如果离职在财季之后，就按财季结束日期计算）
• 员工的实际在职开始日期：取「离职日期」和「财季开始日期」的较大值（如果离职在财季之前，开始日期会大于结束日期，最终天数为0）
• 最后计算有效天数：如果实际结束日期 ≥ 实际开始日期，就用「结束日期 - 开始日期 + 1」（+1是因为要包含首尾两天），否则返回0

基础公式（已知财季起止日期） #

假设：

• 财季Q1开始日期存在单元格 A2（2018/2/4）
• 财季Q1结束日期存在单元格 B2（2018/5/5）
• 员工离职日期存在单元格 C2

直接使用以下公式：

=MAX(0, MIN(C2, B2) - MAX(C2, A2) + 1)

验证你的例子 #

1. 当离职日期为2018年4月2日时：
   • MIN(2018/4/2, 2018/5/5) = 2018/4/2
   • MAX(2018/4/2, 2018/2/4) = 2018/2/4
   • 计算结果：2018/4/2 - 2018/2/4 + 1 = 58，和你预期的结果完全一致
2. 当离职日期为2018年5月7日时：
   • MIN(2018/5/7, 2018/5/5) = 2018/5/5
   • MAX(2018/5/7, 2018/2/4) = 2018/2/4
   • 计算结果：2018/5/5 - 2018/2/4 + 1 = 91，符合预期

进阶：匹配任意财季的公式 #

如果你已经建立了包含「季度编号」「财季起止日期」的表格（假设在Sheet2，A列是季度编号如Q1，B列是开始日期，C列是结束日期），想要根据指定季度自动匹配起止日期，可以用XLOOKUP（Excel 365/2021及以上版本支持）：
假设当前单元格D2是要计算的季度编号（如Q1），公式如下：

=MAX(0, MIN(C2, XLOOKUP(D2, Sheet2!A:A, Sheet2!C:C)) - MAX(C2, XLOOKUP(D2, Sheet2!A:A, Sheet2!B:B)) + 1)

如果你的Excel版本不支持XLOOKUP，可以替换成VLOOKUP：

=MAX(0, MIN(C2, VLOOKUP(D2, Sheet2!A:C, 3, FALSE)) - MAX(C2, VLOOKUP(D2, Sheet2!A:C, 2, FALSE)) + 1)

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