求助：365天生产日期码转有效期的Excel公式修正与适配新码MYDDD

核心公式（自动识别新旧码） #

假设生产日期码在A1单元格，计算+91天有效期的公式如下：

=TEXT(IFERROR(
    LET(
        code,A1,
        y,2000+RIGHT(code,1)*1,
        m_code,LEFT(code,1),
        m,IF(CODE(m_code)>CODE("I"),CODE(m_code)-CODE("A"),CODE(m_code)-CODE("A")+1),
        doy,MID(code,2,3)*1,
        prod_date,DATE(y,1,doy),
        IF(MONTH(prod_date)=m,prod_date,NA())
    ),
    LET(
        code,A1,
        y,2000+MID(code,2,1)*1,
        m_code,LEFT(code,1),
        m,IF(CODE(m_code)>CODE("I"),CODE(m_code)-CODE("A"),CODE(m_code)-CODE("A")+1),
        doy,MID(code,3,3)*1,
        prod_date,DATE(y,1,doy),
        IF(MONTH(prod_date)=m,prod_date,NA())
    )
)+91,"yyyy-mm-dd")

公式逻辑拆解 #

• 自动适配编码规则：通过IFERROR先尝试旧码（MDDDY）解析，失败则自动切换到新码（MYDDD）解析
• 月份字母转数字：将A-M（不含I）转换为1-12月：
  • A-H直接对应1-8月：CODE(m_code)-CODE("A")+1
  • J-M对应9-12月：跳过I，用CODE(m_code)-CODE("A")
• 生产日期校验：生成日期后验证月份是否与编码中的M匹配，避免解析偏差
• 格式化输出：用TEXT将最终有效期转为yyyy-mm-dd的可读格式

调整有效期时长 #

若需计算+180天的有效期，仅需将公式中的+91替换为+180即可。

注意事项 #

• 编码需符合规则：旧码为字母(A-M不含I)+3位年积日+1位年份尾号，新码为字母(A-M不含I)+1位年份尾号+3位年积日
• 当前公式默认年份尾号2/3对应2022/2023年，后续年份更新时，修改2000+为对应基准年即可（如2010+）

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