# 不论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?

• 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).

``````=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"))))))))))))))
``````

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.