一季度的周数 [英] Week number of a quarter

问题描述

我正在尝试根据日期获取给定季度的星期数.
我目前有这个公式

I'm trying to get the week number of a given quarter based on the date.
I currently have this formula

=1+(WEEKNUM(EDATE(Y4,-1)))-(WEEKNUM(DATE(YEAR(EDATE(Y4,-1)), 
LOOKUP(MONTH(EDATE(Y4,-1)),{1,4,7,10}),1)))

但是对于一月，应该给我1但给我10.有什么建议吗?

But for January, it should be giving me 1 but it's giving me 10. Any suggestions?

推荐答案

您希望它在季度开始和结束时如何运作?默认的WEEKNUM函数从每年1月1日的第1周开始，而第2周从1月1日之后的下一个星期日开始.

How do you expect this to work at the start and end of the quarter? Default WEEKNUM function starts week 1 on the 1st of January every year and week 2 starts on the next Sunday after 1st January.

假设您的季度周数应该以相同的方式运行，即，第1周从1月/4月/7月/10月1日开始，而第2周从下一个星期日开始，那么这实际上等同于计数从上个季度开始的6天以来的星期日.

Assuming your quarter week numbers should work the same way, i.e. week 1 starts on the 1st of Jan/Apr/Jul/Oct and week 2 starts on the next Sunday then that's actually equivalent to counting Sundays since 6 days back into the previous quarter.

您可以使用NETWORKDAYS.INTL函数来执行此操作，即使用以下公式:

You can do that using NETWORKDAYS.INTL function, i.e. with this formula:

=NETWORKDAYS.INTL(EOMONTH(Y4,MOD(1-MONTH(Y4),-3)-1)-5,Y4,"1111110")

将结果格式设置为不带小数位的数字

format result as number with no decimal places

NETWORKDAYS.INTL函数在Excel 2010和更高版本中可用-对于较早版本的Excel，您可以使用以下公式获得相同的结果:

NETWORKDAYS.INTL function is available in Excel 2010 and later versions - for older versions of Excel you can get the same results with this formula:

=INT((13-WEEKDAY(Y4)+Y4-EOMONTH(Y4,MOD(1-MONTH(Y4),-3)-1))/7)

这篇关于一季度的周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！