☰

在 Excel 中确定财政年度中的周数

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中确定财政年度中的周数

我正在尝试获取 2014 年 2 月 1 日至 2015 年 1 月 31 日期间数据集的财政年度第 1 至第 53 周的数据。我将财政年度的第一天作为一个单独的星期。

该=WEEKNUM()函数给出了日历年的一周，但为财政年度创建了重叠。例如，在正常的日历中，五周将被标记为 2014 年 2 月 1 日和 2015 年 1 月 31 日。

数据集示例：

Transaction_Date
2/1/14
2/2/14
3/5/14
10/2/14
1/1/15

期望结果：

Transaction_Date    Week_Of_Fiscal_Year
2/1/14               1
2/2/14               2
3/5/14               6
10/2/14             36
1/1/15              49
1/31/15             53

答案1

我的方法是将财政年度“转移”到日历年的开始，因此weeknum计算正确的结果：
=WEEKNUM(A2-31,10+WEEKDAY(DATE(YEAR(A2-31),1,2),2))，其中：

  • A2-31是“调整”日期：将所有内容提前 31 天，这样 2 月 1 日将成为 1 月 1 日
  • WEEKDAY(DATE(YEAR(A2-31),1,2),..)这是为了获取当前财政年度的第一天（因为二月一日是第一周，第二周从二月二日开始，因此第一天也是转换日期（一月二日）的后一天）
    • 10+WEEKDAY(...,2))
    • 第二个参数WEEKNUM是一周的第一天，11 表示星期一，12 表示星期二...
    • 第二个参数WEEKDAY是一周的开始日期，需要使用2MONDAY
    • 10+将 1-7 转换为 11-17

此公式适用于每年，财政年度的开始于 2 月 1 日，第二周的开始于 2 月 2 日在公式中是“硬编码”的。

答案2

我开始研究 WEEKNUM 翻译，处理日历年份转换等，然后意识到还有另一种简单直接的方法：

在此处输入图片描述

A 列是日期。B 列是财政年度的周数。财政年度的第一天（2 月 1 日）在 A2 中输入为参考日期，并且始终位于第 1 周，因此可以在 B2 中进行硬编码。

后续日期的公式可以从 B3 复制下来，即：

=CEILING((WEEKDAY(A$2)+A3-A$2)/7,1)

解释

这仅依赖于交易日期和财政年度第 1 天之间的差异，以及一周包含 7 天的事实。WEEKDAY 函数考虑财政年度从星期几开始，日期差异和 CEILING 函数处理由于交易发生在星期几而产生的小数周差异。

由于您可以控制交易日期输入，因此没有必要对下一日历年 1 月 31 日以后的日期进行错误检查。

______

没有参考日期的解决方案

如果您不想要求输入财务年度的第一天，您可以从交易日期中得出。例如，在 B3 中，您可以使用以下代码，而不是引用单元格 A$2：

DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1)

这将根据交易日期确定财务年度，并将其转换为该年的 2 月 1 日。原始解决方案中的简单公式将变为：

=CEILING((WEEKDAY(DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1))+A3-DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1))/7,1)

答案3

我找到了一个更简单的解决方案，可以根据财政年度 4 4 5 时间表计算周数。

=ISOWEEKNUM(TODAY()-301)

-301 表示对财政日历的调整，就好像您的日历从一月开始一样。在我们的例子中，财政年度通常从十月底开始，因此日期的调整幅度较大。因此，根据您的年份开始时间以及您是否滞后一年，您需要添加或减去它才能使其正常工作。无论哪种方式，这都是简单的数学运算。

显然，如果您想计算单元格内的日期值，那么只需将 TODAY() 更改为您的单元格引用。=ISOWEEKNUM(A1-301)

答案4

我创建了这个 Excel 公式，根据 NRF 4-5-4 日历计算财政周数（通常从公历二月的第一周开始）并占第 53 周（如果第 52 周结束后，1 月份还剩下 4 天，则每 4-6 年发生一次）。

其中单元格“A1”是您的日期：

=ROUNDUP((((A1-28)-WEEKDAY(A1-28)+1)-DATE(YEAR((A1-28)-WEEKDAY(A1-28)+1),1,0))/7,0)

如果您尝试使用单个输入，也可以将“A1”替换为“Today()”。

相关内容