结论
有没有办法使用WEEKNUMExcel 中的函数简单地根据 7 天的时间段计算周数，而不考虑第一天是在哪个星期几？
语境
我正在处理 Excel 中的一些非常大的数据文件（每个文件包含超过 800,000 行数据）。
每个文件中的两列用于计算周数（一列根据以下数据计算周数：公历年，另一个则基于财政年度）。在这种情况下，财政年度从 7 月 1 日至 6 月 30 日。
在这两种情况下，“年份”的值都基于同一Form_Finalised_Date列，并且周数是根据七天的实际天数来计算的（即，1 月 1 日（就日历年而言）或 7 月 1 日（就财政年度而言）是哪个星期几完全无关紧要）。
解释一下，对于日历年来说，这意味着：
- 1 月 1 日至 7 日始终为第 1 周
- 1 月 8 日至 14 日始终为第 2 周
- 1 月 15 日至 21 日始终为第 3 周
- 等等。
就财政年度而言：
- 7 月 1 日至 7 日始终为第 1 周
- 7 月 8 日至 14 日始终为第 2 周
- 7 月 15 日至 21 日始终为第 3 周
- 等等。
我当前的日历年公式 - 它是准确的，但是......
就计算日历年周数的公式而言，我必须满足以下要求：
=IF([@[Calendar Year]]=2015,WEEKNUM([@[Form_Finalised_Date]],14),
IF([@[Calendar Year]]=2016,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2017,WEEKNUM([@[Form_Finalised_Date]],17),
IF([@[Calendar Year]]=2018,WEEKNUM([@[Form_Finalised_Date]],11),
IF([@[Calendar Year]]=2019,WEEKNUM([@[Form_Finalised_Date]],12),
IF([@[Calendar Year]]=2020,WEEKNUM([@[Form_Finalised_Date]],13),
IF([@[Calendar Year]]=2021,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2022,WEEKNUM([@[Form_Finalised_Date]],16),
IF([@[Calendar Year]]=2023,WEEKNUM([@[Form_Finalised_Date]],17),
IF([@[Calendar Year]]=2024,WEEKNUM([@[Form_Finalised_Date]],11),
IF([@[Calendar Year]]=2025,WEEKNUM([@[Form_Finalised_Date]],13),
IF([@[Calendar Year]]=2026,WEEKNUM([@[Form_Finalised_Date]],14),
IF([@[Calendar Year]]=2027,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2028,WEEKNUM([@[Form_Finalised_Date]],16),
"N/A"))))))))))))))
如您所见，为了获得所需的日历年周数结果，我必须确定 2015 年至 2028 年期间每年 1 月 1 日是星期几，这样我就可以添加相应的返回类型年度争论。
然而，尽管这种方法可行，但它：
- 无法适应计算财政年度周数（至少我还没有找到办法）
- 似乎没有必要那么长，因为我必须写它以便在接下来的八年里尝试并为它提供未来保障！
相反，我希望有一个更简单的公式，只需查看列中的日期Form_Finalised_Date，然后通过有效地从 1 月 1 日开始并将每 7 天算作一周等来计算周数。这样，随着数据源中出现更多年份，就无需手动添加更多IF函数。
我目前的财政年度公式 - 简洁，但不是 100% 准确
在花费大量时间尝试调整我的日历年公式来计算财政年度周数后，我最终放弃了，并想出了另一个看起来非常接近我需要的公式。此公式如下：
=WEEKNUM([@[Form_Finalised_Date]]-(1 + DATE(YEAR([@[Form_Finalised_Date]]),6,30)-DATE(YEAR([@[Form_Finalised_Date]]),1,1)))
但是，该公式并不总是能计算出正确的结果（更多详细信息请参见下面的屏幕截图）。
在上面的例子中：
- C 列显示了上面我用于计算日历年周数的非常长的公式的结果。结果准确，但由于我使用函数
IF根据年份指定计算，因此这确实不太理想，因为这意味着每当我们获得不同年份的数据时，我们都需要手动更改公式。我似乎也无法将其调整为计算财政年度周数。 - E 列显示期望结果计算财政年度周数（记住财政年度从 7 月 1 日到 6 月 30 日）。
- F 列显示了我当前计算财政年度周数的公式的结果，但它并不总是产生正确的结果（参见表示计算错误的红色圆圈）。
问题
简而言之，有没有办法使用 Excel 中的 WEEKNUM 函数根据 7 天的时间段简单地计算周数。在我的特定场景中，我需要能够从 1 月 1 日起执行日历年的此操作，以及从 7 月 1 日起执行财政年度的此操作。
笔记：
- 期望的结果是，根据 7 天的时间段来计算周数，而不考虑 1 月 1 日或 7 月 1 日是哪一天。
- 由于我的日历年份公式有效（尽管有点笨拙），所以我主要关心的是获得一个可以准确计算财政年度周数的公式。
答案1
您可以将特定开始日期以来的天数和周数计算为当前日期与指定的开始日期之间的差值。
我创建了这个：
在顶部，我有两个命名单元格：
- cal_year_start_month
- fin_year_start_month
日期列仅包含数据。
‘日期年份’公式：
=YEAR([@date])
“日历年的第一天”公式：
=DATE([@[year of date]],cal_year_start_month,1)
“一年中的天数”公式是“日期”和“日历年的第一天”之间的差异：
=DAYS([@date],[@[first date of calendar year]])+1
‘一年中的第几周’ 是：
=ROUNDUP([@[day of year]]/7,0)
“财政年度的第一天”是：
=DATE(IF(MONTH([@date])<fin_year_start_month,[@[year of date]]-1,[@[year of date]]),fin_year_start_month,1)
‘财政年度的日期’ 是：
=DAYS([@date],[@[first day of financial year]])+1
‘财政年度的周数’ 是：
=ROUNDUP([@[day of financial year]]/7,0)
“第 1 周”列就在那里，所以我可以过滤数据以粘贴上面的屏幕截图：
=OR([@[week of year]]=1,[@[week of financial year]]=1)
如果您愿意，您可以将它们组合成......
‘日历年的周数’：
=ROUNDUP((DAYS([@date],DATE(YEAR([@date]),cal_year_start_month,1))+1)/7,0)
‘财政年度周数’：
=ROUNDUP((DAYS([@date],DATE(IF(MONTH([@date])<fin_year_start_month,YEAR([@date])-1,YEAR([@date])),fin_year_start_month,1))+1)/7,0)