每两周一次
我在网上看到了很多关于双周计算的帖子。没有帮助的部分是它们通常是在两个已知日期之间计算或使用参考日期。
我正在尝试进行没有指定参考日期的计算，只使用（excel）时间的开始时间 - 1/1/1900，这样我可以输入一个日期并获取它所在的双周期间#和双周开始日期，而不必创建列表。
问题 A：寻求一致的双周配方
使用CEILING，我可以得到给定月份的双周经期，但是我必须使用不同的公式。如果可以归结为一个公式，那将会很有帮助。
2021 年 2 月，适用开始日期每两周的时间段包括：
- O13 = 2/1/21 - 2 月初
- O16 = 2/14/21 - 公式结果 - 计算 2 月第 2 期
=CEILING((O13),14)-13= 2021/1/31 -- 第一个在本月内部分发生且在本月内结束的期间。
=CEILING((O13+1),14)+1 = 2021/2/14 -- 本月内第二个期间 [在单元格 O16 中，如下所示]
=CEILING((O16+1),14)+1 = 2021/2/28 -- 本月内开始的第三个期间
这些日期是正确的，但我更喜欢一个可以填写的单一公式，其中二月开始之前的那段时间是一个不同的公式。
问题 B：给定任意一个日期的双周序列号
计算 1 至 26（有时是 27 [大约每 11 年]）的双周周期数。鉴于双周数会在每年的第一个周期重置，我还没有找到有关如何执行此操作的任何有用信息。例如，使用上面的数据：
- 2021 年 1 月 31 日 = 第 3 个时期
- 2021 年 2 月 14 日 = 第 4 个时期
- 2021 年 2 月 28 日 = 第 5 个时期
尝试过：
=quotient(weeknum(biweekly start date))除了某些年份外，几乎都能得到正确的结果，例如 2012 年、2023 年 - 一年恰好有 27 个时期，但 2000 年也有 27 个时期，因此商给出了正确答案。
首先尝试在公式（逻辑和计算）中解决这个问题，然后将其转移到 VBA。因此，如果 VBA 中有更简单的方法，我也会采用。
答案1
这些单一公式计算双周日期：
：： 警告 ：：
由于 OP 没有分享任何示例数据或屏幕截图，因此我展示了在两种不同情况下计算双周日期的方法。
方法 1：
单元格 Q4 中的公式：
=IF(MONTH(N4+14)<>MONTH(N4),DATE(YEAR(N4),MONTH(N4)+1,1),N4+14)
方法 2：
单元格 S4 中的公式：
=IF(AND(DAY(S3)=1,S3-S2<14),S2+14,MIN(S3+14,EOMONTH(S3,0)+1))
注意：
- 保持单元格 S2 空白并在单元格 S3 中输入开始日期。
- 您可以根据您的要求使用其中任何一个。
- 根据需要调整公式中的单元格引用。
答案2
我一直没有跟进我最初提出的关于计算双周周期的问题。我的问题是使用单一公式（而不是最初发布的 3 个不同公式）返回给定月份的双周周期的开始日期，并返回给定单一日期的双周周期编号。目标是在不创建年度双周周期查找列表的情况下完成这两项工作。
以下是我对这些问题的回答，以及我观察到的有关双周期间的所有信息。我想所有这些信息都在网上的某个地方，但最好把它们放在一个地方。所有公式都针对各种日期样本进行了测试。稍加调整，其中一些公式也可以用于支付日期。
有些公式似乎回答了同一个问题，但它们有所不同，因为给出的可用信息不同，或者只是解决问题的方法不同。我相信可能还有更多方法。其中一些公式是我拼凑起来的，其他公式是在网上找到的，我已经不记得它们来自哪里了。
编辑于 3/20/23 ---包含所有这些内容的 XLS 文件
假设/已知
- “Biweekly” = 每两周。
- 每两周的周期从“时间的开始”开始，对于 Excel = 1/1/1900。
- 每两周的课程从第一个星期日开始，于第二个星期六结束。
- “双周”、“双周期”（BPD）或“付款期”（PPD）含义相同，可以互换。
- 在公式中：“tYear” = 所讨论的年份；“tMonth” = 所讨论的月份
- 一年有 26 或 27 个双周周期。27 个周期发生在
- 第一个经期从 1 月 1 日开始，或
- 当第一个时期从 1 月 2 日开始时，并且是闰年。
- 每个月共有 2 或 3 个双周周期。
- 每两周一次的开始日期等于或晚于给定年份或月份的第一天。
- 双周周期的结束日期等于或晚于给定年份或月份的最后一天。
- 出现以下情况时，必须进行公式补偿：
- 一年有 27 个时期
- 一年接着一年，有 27 个时期
- 每两周跨几年（例如 12/29/24-1/11/25）
问题 1：退货开始日期每两周一次给定月份用一个奇异公式：
使用单元格 AN17 中的公式：
=IF(EOMONTH(DATE(tYear,tMonth,1),0)-AN16<14,"---", CEILING(DATE(tYear, tMonth,1)-1,14)+1+14*(ROW(AN2)-2))
2021 年 1 月：
| 排＃ | AN 柱 |
|---|---|
| 16 | 20-12-20 星期日 |
| 17 | 21 年 1 月 3 日 星期日 |
| 18 | 17-1月-21 星期日 |
| 19 | 31-1月-21 星期日 |
2021 年 2 月：
| 排＃ | AN 柱 |
|---|---|
| 16 | 31-1月-21 星期日 |
| 17 | 14-二月-21 星期日 |
| 18 | 28-二月-21 星期日 |
| 19 | --- |
*第 16 行给出了给定月份内上一个月的 BPD 开始时间。
结果IF(EOMONTH…..<14为“----”，因为月份可以有 2 个或 3 个双周周期。
也许不太优雅，但似乎有效。
问题 2：归还数字给定任何 BPD单数日期
其中 BPD 的编号是其在给定年份内的序列号，通常每年有 26 个，有时有 27 个。
为了使用单数公式，计算给定日期所在的 BPD 的开始日期：
=FLOOR(GivenDate - 1, 14) + 1
然后是 BPD 编号：
=QUOTIENT(WEEKNUM(StartDate+1),2)
补偿 27 个双周周期之后的年份：
=IF(DAY(CEILING(DATE(YEAR(StartDate+1),1,1)-1,14)+1) =1, 1, 0)
全部合并为一个公式：
=QUOTIENT(WEEKNUM(FLOOR(GivenDate-1,14)+1),2)+IF(DAY(CEILING(DATE(YEAR(FLOOR(GivenDate-1,14)+1),1,1)-1,14)+1) =1, 1, 0)
其他（可能）有用的公式
生成以给定月份（“tmonth”）和年份（“tyear”）开始的双周周期列表：
| 开始 | 结尾 | PPD#（代表“开始”） | PPD#（代表“结束”） |
|---|---|---|---|
| 31-1月-21 星期日 | 13-二月-21 星期六 | 3 | 3 |
| 14-二月-21 星期日 | 27-二月-21 星期六 | 4 | 4 |
| 28-二月-21 星期日 | 21 年 3 月 13 日 星期六 | 5 | 5 |
双周周期“开始”
=CEILING((CHOOSE(N(ISTEXT(A6))+1,A6, DATE(tyear,tmonth,1)-15)),14)+1
A6 = 标题行；公式输入到 A7 并向下填充
如果 CHOOSE 得出 2，表示第一行数据，它将返回给定月份/年份第一天之前的双周周期 [DATE(tyear,tmonth,1)-15]。（仅供参考）
双周周期“结束”
只需将 13 添加到开始日期即可。（后面将介绍计算结束日期的其他公式。）
双周周期数 (1 - 26)（根据开始日期）
=QUOTIENT(WEEKNUM(StartDate),2)+IF(DAY(CEILING(DATE(YEAR(StartDate),1,1)-1,14)+1) =1, 1, 0)
双周周期数 (1 - 26)（基于结束日期）
由于双周周期可能跨越不同的年份，因此公式与基于状态日期的公式略有不同。
=QUOTIENT(IF(YEAR(EndDate)>YEAR(StartDate),WEEKNUM(StartDate),WEEKNUM(EndDate)-1),2)+IF(DAY(CEILING(DATE(YEAR(StartDate),1,1)-1,14)+1) =1, 1, 0)
给定一年，返回第一个/最后一个双周周期的开始/结束日期。 无需查找列表。
| 给定年份 | 第一个 ppd 开始日期 | 上次 ppd 开始日期 | 上次 ppd 结束日期 | # pds/年 | 最后一个 pd 的 pd # |
|---|---|---|---|---|---|
| 1900 | 00 年 1 月 1 日 星期日 | 2000 年 12 月 30 日 星期日 | 01 年 1 月 12 日 星期六 | 二十七 | 二十七 |
| 2000 | 2000 年 1 月 2 日 星期日 | 2000 年 12 月 31 日 星期日 | 01 年 1 月 13 日 星期六 | 二十七 | 二十七 |
| 2001 | 2001 年 1 月 14 日 星期日 | 30-12-01 星期日 | 02 年 1 月 12 日 星期六 | 二十六 | 二十六 |
| 2002 | 2002 年 1 月 13 日 星期日 | 2002 年 12 月 29 日 星期日 | 03 年 1 月 11 日 星期六 | 二十六 | 二十六 |
给定年份的第一个双周期间开始日期
=CEILING(DATE(tyear,1,1)-1,14)+1
给定年份的最后双周期间开始日期
=FLOOR(DATE(tyear,12,31)-1,14)+1
给定年度结束日期的最后双周期间
=CEILING(DATE([year],12,31),14)
一年中双周经期总数（26 或 27）
=(DATEDIF([1st Start Date],[Last End date],"d")+1)/14
或者…
一年中最后一个双周的周期数（26 或 27）
=TRUNC((([Last Pd Start]-First Pd Start])+13)/14)+(WEEKDAY([Last Pd Start]))
确定年份是否为闰年
=IF(OR(AND(MOD(tyear,4)=0,MOD(tYear,100)>0),MOD(tYear,400)=0),"Leap","Regular")
给定任意单数日期，返回该日期双周周期的开始/结束日期。 无需查找列表。
其中：开始 <= 给定日期 <= 结束开始
： =FLOOR(GivenDate-1,14)+1
结束： =CEILING(GivenDate,14)
期间编号：= 回答上述问题 2 中的答案。
给定年份和双周期间编号 (BPD#)，返回该期间的开始/结束日期。无需查找列表。
方法：计算一年中第一个时期的开始日期，然后添加给定每个时期的天数，即 14 天（将时期号乘以 14 的乘积）。
开始： =CEILING(DATE(tyear,1,1)-1,14)+1+(14*(BPD#-1))
结束：=CEILING(DATE(tyear,1,1)-1,14)+(14*BPD#)
给定一个月，返回该月的双周周期（或发薪日）数。 无需查找列表。
*大多数月份有 2 个双周周期，但 2 个月会有 3 个。
方法：计算月第一个周期的开始日期和月最后一个周期的结束日期。确定天数，除以 14。
| 给定月份 | 第 1 次 PPD 开始 | 最后一次 PPD 结束 | 当月 ppd 数量 |
|---|---|---|---|
| 1 | 21 年 1 月 3 日 | 21 年 2 月 13 日 | 3 |
| 2 | 21 年 2 月 14 日 | 21 年 3 月 13 日 | 2 |
| 3 | 21 年 3 月 14 日 | 21 年 4 月 10 日 | 2 |
月内第一个经期的开始日期： =CEILING(DATE(tyear,tMonth,1)-1,14)+1
最后一个经期的结束日期： =CEILING(EOMONTH(DATE(tyear,tMonth,1),0),14)
每月经期数： =(DATEDIF(StartDate,EndDate,"d")+1)/14
参考文献和注释
https://exceljet.net/formula/next-biweekly-payday-from-date
*基于 Excel 的开始时间 1900 年 1 月 1 日。
=CEILING(date + 1, 14) - 1 其中日期为任意日期（返回第二个星期五）
=CEILING(date - 1, 14) + 1 （返回第二个星期日）
我的注释：
看起来 (日期 +/-1) 要么排除 (+)，要么包含 (-) 开始日期。
Ceiling(….,14)得出第二个星期六（星期六是第 14 天）；7 得出第一个星期六。
结尾 (+/-1) 是因为CEILING结果是星期六，但想要的是星期五 (-) 或星期日 (+)。
该CEILING函数将数字向上舍入到给定的倍数（例如 14）。它之所以有效，是因为日期在 Excel 默认的 1900 日期系统中的工作方式是这样的，其中系统中的第一天是数字 1，等于日期 1900 年 1 月 1 日星期日。
在这个方案中，第一个星期五是第 6 天，第二个星期五是第 13 天，第 14 天是第二个星期六。这意味着未来的所有第二个星期六都可以被 14 整除。
上面的第一个公式使用这个事实来计算第二个星期六（* 工资期结束），然后减去 1 得到前一个星期五。
要获取奇数星期五，请使用：
=CEILING(date+8,14)-8
公式需要向前滚动 8 天才能获得 14 的偶数倍数（因为第二个星期五距第一个星期六仅 6 天 [6+8=14]）。CEILING返回日期后，减去 8 天即可返回上一个星期五。
链接电子表格：
包含所有这些公式 + 样本日期。许多列要么用于测试我创建的公式，即确认其正确性，要么用于找出为什么我没有得到预期的结果。XLS
中的日期“列表”不是用于查找日期，而是用于测试公式，因为目标是生成返回正确结果的公式，而无需创建“查找列表”。它们对于在尝试的公式失败时进行观察很有用。