将日期转换为会计年度、会计期间和会计周的公式

Question

处理一个将接受日期并将其转换为FYxxPxxWx格式的公式。

例如。输入日期03/22/20，公式会给出正确的FY20P06W4。

但是，如果您输入02/02/20，该公式将为您提供FY20P05W2。正确的输出应该是FY20P05W1。这个问题的日期也是09/29/19。它给你的是FY20P12W5。正确的输出应该是FY20P1W1。

当你输入日期04/5/20时，会发生一些奇怪的事情，当它应该是FY20P07W2的时候，你会得到FY21P07W2。

公式是=CONCATENATE("FY",RIGHT(YEAR(DATE(YEAR(D5),MONTH(D5)+(10-1),1)),2),"P",TEXT(CHOOSE(MONTH(D5),4,5,6,7,8,9,10,11,12,1,2,3),"0#"),"W",WEEKNUM(D5,1)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1),1)+1)

我认为这个问题是由奇怪的几周引起的，当一个月结束时，另一个开始抛出公式。

我有一个公式来计算财政年度的开始日期。

=(DATE(YEAR(TODAY())-1,10,1)-(WEEKDAY(DATE(YEAR(TODAY())-1,10,1),1)))+1

这将输出09/29/19作为会计年度的开始日期，因为它与会计年度的第一个月10/1/19是同一周。如果这有意义的话。

单独的公式用于FY和grabs，仅用于年份RIGHT(YEAR(DATE(YEAR(D5),MONTH(D5)+(10-1),1)),2)的最后两位数

对于句号(给我一个两位数的句号TEXT(CHOOSE(MONTH(D5),4,5,6,7,8,9,10,11,12,1,2,3),"0#")

对于星期WEEKNUM(D5,1)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1),1)+1)

Answer 1

我相信我有一个解决方案给你。接下来的讨论，但这里是完整的公式：

=CONCAT("FY",RIGHT(YEAR(D5+91+WEEKDAY(DATE(YEAR(D5),10,1))),2),"P",TEXT(IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5),IF(MONTH(D5)=9,1,CHOOSE(MONTH(D5),5,6,7,8,9,10,11,12,1,2,3,4)),CHOOSE(MONTH(D5),4,5,6,7,8,9,10,11,12,1,2,3)),"0#"),"W",ROUNDUP(((D5-IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5),DATE(YEAR(D5),MONTH(D5)+1,1)-WEEKDAY(DATE(YEAR(D5),MONTH(D5)+1,1))+1,DATE(YEAR(D5),MONTH(D5),1)-WEEKDAY(DATE(YEAR(D5),MONTH(D5),1))+1))/7)+0.01,0))

一个问题是，这仍然是计算2/2/2020的方式，你说的是不正确的。但是，当我根据日历验证它时，似乎FY20P05W02应该是正确的。如果包括每月一号的一周开始一个新的支付期，这意味着2/ 1 /2020，落在周六，将是第一财周的最后一天。这将使2/ 2 /2020成为第二财周的第一天。

为了计算财政年度，我使用了RIGHT(YEAR(D5+91+WEEKDAY(DATE(YEAR(D5),10,1))),2)。因为您可以指望从10月初到12月底总是有91天，所以它有助于这个计算。在你的公式中，你有MONTH(D5)+(10-1)，这将使你在IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5)中超过9个月。这就解释了为什么你2020年4月5日的结果落后了一年。

财务期有点棘手，需要几个嵌套的IF语句。我首先使用IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5)来说明月底将进入下一财年的天数，然后使用IF(MONTH(D5)=9来说明9月底可能会进入下一财年的几天。9月底的天默认为1，包含在下一个会计期间的月末天数使用第一个CHOOSE函数(它们需要下个月的数字)，其他所有内容都会在您编写该函数时获取CHOOSE函数。

会计周花费了更多的时间，但最后我评估了当前会计月的开始，并从D5中的日期中减去它，然后除以7，再加上0.01，这样偶数就可以正确地四舍五入。

我花了几年的时间对它进行了测试，它似乎运行正常，但如果你有问题或问题，请告诉我。

使用WEEKNUM时需要考虑的一件事是，除非您使用option 21或ISOWEEKNUM，否则您将有一个星期在年初被计算两次。它们给出的结果是相同的，并确保任何给定的一天都只分配一个星期数，而不管年份。