如何实现按每月上下半月递增计算账单代码末尾三位00x？

问题描述 #

需要为账单发票生成代码，要求代码末尾三位数字每半个月递增+1。首个账单周期03/16/24-03/31/24对应代码ACE-2024-001，后续周期示例：

• 04/1/24-04/15/24 → ACE-2024-002
• 04/16/24-04/30/24 → ACE-2024-003

原有公式仅按31天递增，不符合需求，需调整。

解决方案公式 #

使用以下Google Sheets公式（假设A2为账单周期内的任意日期）：

=IF(A2="","","ACE-"&YEAR(A2)&"-"&TEXT(ROUNDUP(DATEDIF(DATE(2024,3,15),A2,"D")/15,0),"000"))

公式逻辑解析 #

• IF(A2="","",...)：若A2单元格为空，返回空值；否则生成发票代码。
• "ACE-"&YEAR(A2)&"-"：拼接固定前缀ACE-、当前年份和连接符-。
• TEXT(..., "000")：将计算出的周期数格式化为三位数字，不足三位时自动补0。
• 核心计算部分：
  • DATE(2024,3,15)：以首个周期的前一天作为基准日期，确保从03/16/24开始计算第一个周期。
  • DATEDIF(DATE(2024,3,15),A2,"D")：计算基准日期到A2日期的天数差。
  • ROUNDUP(...,0)：将天数差除以15后向上取整，得到从1开始的周期序号（自动适配不同月份的月末天数差异，每半个月递增一次）。

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