不论1月1日是星期几，在Excel中使用WEEKNUM函数生成基于7天实际天数的最佳方法

由 发布于

TLDR

Is there a way to use the WEEKNUM function in Excel to simply calculate week numbers on the basis of days 1 to 7 = Wk1, days 8 to 14 = Wk2, days 15 to 21 = Wk3, and so on without any regard whatsoever to what weekday 1st January falls on?

语境

我正在处理Excel中的一些非常大的数据文件（每个文件包含900,000多行数据）。

这些文件中的每个列中的两列旨在计算周数（一个基于日历年计算周数，另一个基于财务年度计算周数）。在这种情况下，财政年度为7月1日至6月30日。

不论专栏：

  • the value for 'year' is based on the same Form_Finalised_Date column
  • the weeks are calculated on the basis of seven actual days (i.e. it is totally irrelevant what weekday the 1st January falls on (in the case of Calendar Years) or 1st July falls on (in the case of Financial Years).

作为解释，这意味着1月1日至7日将始终是第1周，1月8日至14日将始终是第2周，依此类推。并且，在财务年度中，7月1日至7日将始终为第1周，7月8日至14日将始终为第2周，依此类推。

在花了很多时间后，我想出了一个似乎有效且准确的公式来计算财务年度的周数。该公式如下：

=WEEKNUM([@[Form_Finalised_Date]]-(1 + DATE(YEAR([@[Form_Finalised_Date]]),6,30)-DATE(YEAR([@[Form_Finalised_Date]]),1,1)))

但是，我还没有找到我认为对日历年做同样的有效公式。目前，我必须满足以下条件：

=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日是哪个工作日，因此我可以添加Return_type参数值，以便根据需要进行计算（例如，实际7天后的一周）。

However, besides the fact this is an ugly and unnecessarily long formula, I've had to write it to allow for the next eight years. Instead, I was hoping to have a much simpler formula that would just look at the year from the Form_Finalised_Date column and work out the week number by effectively starting on 1 January and counting each 7 days as a week and so on. This way there is no need for manually allowing for the years via the IF function.

我确实尝试调整了我在财政年度周数中使用的公式，但是无论我尝试怎样，它似乎在几年内都无法正确计算（例如，在某些年份中，它将1月7日视为2周）。

有没有一种方法可以在Excel中使用WEEKNUM函数来简单地根据第1至7天= Wk1，第8至14天= Wk2，第15至21天= Wk3等来计算周数？