Excel公式获取月份中的周数(有星期一)

Question

使用 Excel 公式，我需要获取给定日期的月份中的周数。但是，条件是其中应该有星期一。周一至周日为工作日。

我已经尝试过这个:

但是，周数为 5，而应该为 4，因为 2013 年 11 月 1 日是星期五，因此将在 10 月的最后一周计算。

Answer 1

Jonathan from the ExcelCentral forums suggests :

=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1 

This formula extracts the week of the year [...] and then subtracts it from the week of the first day in the month to get the week of the month. You can change the day that weeks begin by changing the second argument of both WEEKNUM functions (set to 2 [for Monday] in the above example). For weeks beginning on Sunday, use:

=WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1

For weeks beginning on Tuesday, use:

=WEEKNUM(A1,12)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),12)+1

etc.

我更喜欢它，因为它使用 Excel 内置的周计算功能 ( WEEKNUM )。