Table # 1
Obs. # Date Weeknum() Start Day Day # Week (Day# / 7)
2 Friday, December 30, 2011 53 1/1/2012 363 51.86
3 Saturday, December 31, 2011 53 1/1/2012 364 52.00
4 Sunday, January 01, 2012 1 1/1/2012 1 0.14
Table # 2
Obs. # Date Weeknum() Start Day Day # Week (Day# / 7)
2 Thursday, December 30, 2004 53 1/9/2004 364 52.00
3 Friday, December 31, 2004 53 1/10/2004 365 52.14
4 Saturday, January 01, 2005 1 1/11/2004 1 0.14
Firstly, i found the weeknum() and weeks by Day# / 7 conflicting. Why is it so?
Secondly we define no. of weeks in a year as Total days divided by 7. To me, the Problem is created when the total number of days divided by 7 gives week num that is not a whole number but a number with decimals that implies that one week (the last one in the year) has less then 7 days and not complete 7 days to meet the criteria of a week. (That fraction of a week is actually 53rd week with days less then 7, and is the overlapping week, where X Days are in week 53rd and 7-X in 1st week of the next year). For example you can see the Obs # 3 in both the tables.
This is where this formula malfunctions (in my view, i haven't checked yours but i am talking about that of mine, it was giving same 53rd week as yours is giving) and hence does not show up with 1st week of the next year, In order to adjust for the fraction of week that is counted extra i think we should added a part that will find if there is one (the fractional part) and will subtract it (that week) from the result so that we may get first week in the next year..Secondly excel weeknum() function does not appear (to me) to recognize that every time a week must start from certain day. It follows dates and withing bracket of certain date it gives week number so is it really possible to start week from some specified day or not?
The summary of the post is that i didn't found it correct (not always) to subtract 01 from the weeknum() that you have used to adjust the formula. Rather 1 should be subtract only when the last week has days less then 7 for that is not the 53rd week of the year but the 1st week of the next year! Kindly check that whether your formula shows up for the first week of the next year or not? (Mine none adjusted always show 2nd week)
I have tried to adjust it here are the formulas: With Your table located from A1:E4
Formula for New Week is :
=WEEKNUM(DATE(B2,1,1)+((A2+C2)*7-IF((((DATE(B2,12,31)-DATE(B2,1,1))/7)-QUOTIENT(((DATE(B2,12,31)-DATE(B2,1,1))/7),1))>0,1,0)))
Formula for New Year is :
=YEAR(DATE(B2,1,1)+((A2+C2)*7))
The above formulas will give following table, plz check table as well as my concept.
[code]Week Year Add Weeks New week Year
50 2012 8 6 2013
50 2012 100 46 2014
22 2013 -22 1 2013