I send you back Faseeh's file with my comments: Would you upload or mail a file, so as it'll be easier for me processing the two following paragraphs without checking formulas in the air -or analyzing it deeeply, remember I tend to be lazy!-? Thanks. I think I covered the "Firstly" and the "Secondly". The WEEKNUM (or week number of the year) may range from 1-53 or from 1-54, this last case just for leap-years beginning on sundays: see years 1928, 1956, 1984, 2012, 2040, and so on(look at the 28 year period.).Ĭ) The difference between a) and b) resides on the fact that for a) weeks start on the week day of Jan 1st (ergo always will be 53) and for b) weeks start on the first monday whose week includes thursday (that's to say first week with 4 days at least).ĭ) So "weeknum() and weeks by Day# / 7 conflicting" is logic.
#Excel week number to date full#
So a year hasn't 52 weeks, it has 53, not all full but not 52.ī) Let's consider that weeks start on monday, to follow user specs about using european notation in this problem.
Or if you like 52.142857 (BTW, did you know that multiplying 142857 by 1 thru 7, you get the same digits in different order? try it) and 52.285714 for leap-years (see the decimal part?). I'm in, eh!Ī) The number of weeks of a year is 53. Thank being paid by char written? Hire me right now, please!!! 25c a word, 610 words, 152 and a half bucks. =WEEKNUM(DATE(B4,1,1)+((A4+C4)*7-1)) Change the week num from 22 to 23 in the just-above table and it will show you 2nd week, 2013. PS: My non adjusted formula for week for the same table is: The above formulas will give following table, plz check table as well as my concept.
I have tried to adjust it here are the formulas: With Your table located from A1:E4 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)
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. 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? 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. For example you can see the Obs # 3 in both the tables. (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). 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.
of weeks in a year as Total days divided by 7.
0 kommentar(er)
