Google Sheets日期计算问题：添加年/月时长并取次月1日

问题背景 #

C2单元格为德式格式（dd.mm.yy）的日期文本，D2单元格为时长（格式如X year/X years或X month/X months），需要在G2计算C2加上D2时长后，将结果向上取整至次月1日。原嵌套IF函数仅在D2为月时长时生效，年时长时会返回#VALUE!错误，原因是FIND函数找不到指定文本时直接报错。

错误原因 #

原函数中使用FIND("month", D2)作为IF判断条件，当D2中不含"month"时，FIND会直接返回#VALUE!错误，导致整个IF分支无法执行，无法进入年时长的判断逻辑。必须用ISNUMBER(FIND(...))来包裹，将查找结果转为布尔值（存在则TRUE，不存在则FALSE），避免报错。

修正后的公式 #

简洁版（使用LET函数简化逻辑）

=LET(
   original_date, DATE(RIGHT(C2,2)+2000, MID(C2,4,2), LEFT(C2,2)),
   duration_value, VALUE(LEFT(D2, FIND(" ", D2)-1)),
   added_date, IF(ISNUMBER(FIND("month", D2)), EDATE(original_date, duration_value), EDATE(original_date, duration_value*12)),
   DATE(YEAR(added_date), MONTH(added_date)+1, 1)
)

传统嵌套IF版（兼容旧版Google Sheets）

=IF(ISNUMBER(FIND("month", D2)), 
   DATE(YEAR(EDATE(DATE(RIGHT(C2,2)+2000, MID(C2,4,2), LEFT(C2,2)), VALUE(LEFT(D2, FIND(" ", D2)-1)))), MONTH(EDATE(DATE(RIGHT(C2,2)+2000, MID(C2,4,2), LEFT(C2,2)), VALUE(LEFT(D2, FIND(" ", D2)-1))))+1, 1), 
   IF(ISNUMBER(FIND("year", D2)), 
      DATE(YEAR(EDATE(DATE(RIGHT(C2,2)+2000, MID(C2,4,2), LEFT(C2,2)), VALUE(LEFT(D2, FIND(" ", D2)-1))*12)), MONTH(EDATE(DATE(RIGHT(C2,2)+2000, MID(C2,4,2), LEFT(C2,2)), VALUE(LEFT(D2, FIND(" ", D2)-1))*12))+1, 1), 
      ""
   )
)

公式说明 #

1. original_date变量：将C2的德式文本日期转为标准日期格式

   • RIGHT(C2,2)+2000：提取两位年份并转为四位（如24→2024）
   • MID(C2,4,2)：提取月份（德式日期中第4-5位是月份）
   • LEFT(C2,2)：提取日（德式日期中前2位是日）

2. duration_value变量：从D2中提取时长数值，通过VALUE转为数字类型，避免文本参与计算出错

3. added_date变量：根据D2中的关键词判断是加月还是加年

   • 含"month"：直接用EDATE加对应月数
   • 含"year"：将年数乘以12转为月数后用EDATE计算

4. 最终结果：通过DATE(YEAR(added_date), MONTH(added_date)+1, 1)直接生成added_date所在月份的次月1日，完成向上取整需求

测试案例 #

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