• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding week numbers in different years

J

Jejo

New Member
I would like to find a formula to add (or subtract) week numbers.


Example: Add 8 weeks to week 50 in year 2012. The answer should be week 6, year 2013


50/2012 + 8 weeks = 6/2013


Add 100 weeks to week 50 in year 2012. The answer should be week 46, year 2014.


50/2012 + 100 weeks = 46/2014


Also: 46/2014 – 100 weeks = 50/2012


Thank you in advance.


JJ
 
Hi, Jejo!


Assume your data starts in cell A1 as follows (date formats are in spanish "ddd dd/mmm/yyyy"):


-----

[pre]
Code: 
Start year	Start week	Start date		Added weeks No.	End date	End year	End week
2012		50		sáb 15/dic/2012		100		sáb 15/nov/2014	2014		46
[/pre]
-----


Try this:

a) in A2 and B2 enter start year and week no.

b) in C2 type: =FECHA(A2;1;1)+B2*7-1 -----> in english: =DATE(A2,1,1)+B2*7-1

c) in D2 enter the weeks no. to be added or subtracted

d) in E2 type: =C2+D2*7

e) in F2 type: =AÑO(E2) -----> in english: =YEAR(E2)

f) in G2 type: =NUM.DE.SEMANA(E2;1) -----> in english: =WEEKNUM(E2,1) for week starting on Sunday, else change last parameter (1)


Hope it's suitable for your issue.


Regards!
 
Hi, Jejo!

Glad you solved it.

Perhaps you may want to read the three green sticky posts at this forums main page. You'll find general guidelines about introducing yourself, posting and uploading sample files.

Regards!


EDIT: Made a mistake in parameter description of WEEKNUM function, Sunday is 1, not Monday as I previously stated wrongly. It's updated in the previous post. Sorry.
 
Thank you for your tips.


There seems to be a problem with the week numbers.


Example: Subtract 22 weeks from week 22 year 2013. The answer is week 54 year 2012.

(22/2013 – 22 weeks =54/2012)


There are definitely not 54 weeks in a year.


How can I solve this problem? I would like to keep it to 52 weeks in a year and not 54.
 
Hi, Jejo!


I typed the formulas in an empty sheet and I get 53 (which is right) and not 52.

Why?


First, let's assume that our weeks start on Sundays (WEEKNUM second parameter = 1). But the analysis is valid for any starting day.


Second, in your example the real dates are Monday, June 3rd. 2013 (second day of week 22) and Monday, December 31st. 2012 (second day of week 53).


Third, all non leap-years have 53 weeks (1 week of X days -with X from 1 to 7-, 51 full weeks of 7 days, 1 week of Y days -with Y=7-X+1 so as to totalize 365 days-). But if it's a leap-year, it starts on Saturday (last day of the week), then you'll have 54 weeks (1st. of 1 day, 52 of 7 days, and last of 1 day).


2012 it's a leap-year but doesn't start on Saturday, but what about 2000? Leap-year, starting on Saturday, ending on Monday, 54 weeks.


Do we agree?


Regards!
 
Hi, Jejo!


Going on thinking after posting, I realize that the date calculated for the beginning of the period is in the middle of the week that started on previous Sunday. And I'm doubting if it's right that it starts on Monday -or any other day and not in Sunday-.

I'm adjusting this. Write you in a few minutes. I hope.


Regards!
 
Hi, Jejo!


New data updated:

-----

[pre]
Code: 
Start year	Start week	Start date	Added weeks no.	End date		End year	End week
2012		50		dom 16/dic/2012	100		dom 16/nov/2014		2014		47
2013		22		dom 02/jun/2013	-22		dom 30/dic/2012		2012		53
[/pre]
-----


Now the starting dates occur on the predefined week starting date (Sunday), and note that in the first case now week is 47 (which it's OK), and in the second still 53 (which it's OK too).


Update formula in C2 (and down) as:

=FECHA(A2;1;1)-DIASEM(FECHA(A2;1;1);1)+1+B2*7 -----> in english: =DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),1)+1+B2*7


Where:

DATE(A2,1,1) is the date for Jan 1st.

WEEKDAY(DATE(A2,1,1),1) is the week day for Jan 1st.

+1 is the adjusting factor to get date of that week Sunday

B2*7 is the number of days added for B2 weeks no.


Now I think it's done. Isn't it?


Regards!


PS: file available for uploading at your disposition, just advise.
 
What about using the Excel Weeknum function?

Code: 
=Weeknum(Date_Cell + Weeks_cell, 2)
 
@Hui


Hi!


I thought I wrote that in my very first answer at the second post of this topic. Had to use extra columns for preparing and checking the input data (year, week no.) and displaying output data in similar format (year, week no.).


Am I right or I'm unable to see my mistake?

Please give a look to f) in related post.


All the other posts were for clarifying doubts on year's week numbers and a minor adjust for starting on Sunday.


Regards!
 
Hi,


I am still having problems with the week numbers.


I read this about the weeknum function:


 "The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard."


I need to calculate the week numbers according to the European Standard for my project.


Do you know how to change the settings to the European Standard?


Thank you for your help until now.


JJ
 
Hi, Jejo!


As Jack said, let's go by parts:

a) we're Ok with the previous explanations of weeks per year? so we can go on from a clear and accepted base

b) WEEKNUM function accept the following second parameter values (you can get it in your own language from within formula wizard):

-----

[pre]
Code: 
Tipo		La semana empieza el	Sistema
1 u omitido	Domingo			1
2		Lunes			1
11		Lunes			1
12		Martes			1
13		Miércoles		1
14		Jueves			1
15		Viernes			1
16		Sábado			1
17		Domingo			1
21		Lunes			2
[/pre]
-----

c) so you firstly must define which starting day you want to use (European system uses Monday so as to count the majority of days within first week, you can't start week on other day different than Monday -and not Sunday as we were using until now-)


Wait for your comments or decisions.


Regards!
 
Hi SirJB7,


I have understood your explanation of the week numbers. If we use the European System there will only be 52 or 53 weeks in the year.


I need to use the European Sytem:

- week starts on a Monday

- week 1, should have the 'majority of days of the week' should be from January (as you mentioned in your post C.)


JJ
 
Hi, Jejo!

Glad you got it. Do you need further assistance to adequate the formulas or you can manage to handle them? If not, just advise please.

Regards!
 
Hi SirJB7,


I don't know which formula to use to get the European System. Is there a way of changing to the european system on Excel or do you know a formula that I could use?


JJ
 
Hi SirJB7,


Sorry for interruption, but i have a question. Today morning i started to workout the formula for above post and ended up what you had already posted above (with a slight modification, the essence of the formula was the same) but then i found a problem that i am presenting to you here. Lets consider these two table:

[pre]
Code: 
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
[/pre]
PS: My non adjusted formula for week for the same table is:

=WEEKNUM(DATE(B4,1,1)+((A4+C4)*7-1))[/code] Change the week num from 22 to 23 in the just-above table and it will show you 2nd week, 2013.


Thank You,

Faseeh
 
Hi, Jejo!


For using European system you should change the formulas as stated in the following link, changing the second parameter of WEEKNUM function to 2, and adjusting the starting week in column C to retrieve as starting date for each year the Monday that includes in its week Jan 1st from Monday to Thursday.

http://dl.dropbox.com/u/60558749/Adding%20week%20numbers%20in%20different%20years%20%28for%20Jejo%20at%20chandoo.org%29.xlsx


C2 formula:

=FECHA(A2;1;1)-DIASEM(FECHA(A2;1;1);2)+1+SI(DIASEM(FECHA(A2;1;1);2)<=4;0;7)+(B2-1)*7 -----> in english: =DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),2)+1+IF(WEEKDAY(DATE(A2,1,1),2)<=4,0,7)+(B2-1)*7


G2 formula:

=NUM.DE.SEMANA(E2;2) -----> in english: =WEEKNUM(E2,2)


About Faseeh's post, I haven't had time to read it carefully, but I'll do. I suppose he's right as usual.


Regards!
 
@Faseeh


Hi!

Short of time on Mondays, I'll read your Encyclopaedia Britannica new volume tonight.

Regards!
 
@Faseeh


Hi!


Again being paid by char written? Hire me right now, please!!! 25c a word, 610 words, 152 and a half bucks... I'm in, eh!


I disagree about the following concepts:

a) The number of weeks of a year is 53... always. 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?). So a year hasn't 52 weeks, it has 53, not all full but not 52.

b) Let's consider that weeks start on monday, to follow user specs about using european notation in this problem. 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...).

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

d) So "weeknum() and weeks by Day# / 7 conflicting" is logic.


I think I covered the "Firstly" and the "Secondly". 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.


Regards!
 
Hi SirJB7,


Thank You for your reply. :) & sense of humor.


You read my entire post but left just last two line: Why there is no first week in 2013 in this case:

[pre]
Code: 
Start year	Start week	Start date	Added weeks no.	End date		End year	End week
2013		22		dom 02/jun/2013	-22		dom 30/dic/2012		2012		53
[/pre]
Change this 22 to 23 & 24, you will get 54th week of this year and 2nd week of next year, (I might be wrong or there could be some misconception as you pointed in your last reply but..) 'there is no first week of 2013'. I am also attaching a sample file for your ease and comfort :)


http://dl.dropbox.com/u/60644346/Week_Date.xlsx


Regards,

Faseeh
 
Hi SirJB,


Thank you for the new formula, however, there seems to be a problem.


With the European System most years should only have 52 weeks (2012 has 52 weeks, 2013 has 52 weeks etc.). Furthermore, with the formula that you posted earlier the following happens:


Week Year Add Weeks New Year New week

2012 22 0 2012 23 <---week 22 + 0 is not equal to week 23

2013 22 0 2013 22


Certain years have this problem.


How can this be corrected?


JJ
 
Hi, Jejo!


I send you back Faseeh's file with my comments:

http://dl.dropbox.com/u/60558749/Adding%20week%20numbers%20in%20different%20years%20-Week_Date%20%28for%20Jejo%20at%20chandoo.org%29.xlsx


With any system we use (European or not) years will mathematically speaking have 53 weeks (52 & a litte) and Excel WEEKNUM function will range from 1 to 52, 53 or 54, depending on what parameter we use.


In Sheet2 you'll find a table with the last 7 days of each year and the first 7 days of next year, i.e. a 14 days period ranging form Dec 25th thru Jan 7th for years 2000-2020. Columns A:C are used to build date in column D, and with this date WEEKDAY is calculated in all it possible parameters (see row 1) in columns E:O and WEEKNUM is calculated with it all possible parameters (see row 1) in columns O:X

You can analize there how Excel handles weekdays and weeknums accordingly to the criteria defined by the user. Once we define a criteria we should follow it, and if we find that it doesn't fit in our model, well, we have to change the criteria or adapt the model.

What do I mean with this? Changing the criteria is the simple path, just adjust one parameter value in formulas et voilà, c'est tout! Adapting the business model may be harder, but sometimes it's reasonable to do it. It's up to you, I can't tell you how to rule your metier; as I did earlier I'd simply suggest to define a criteria and follow it not matter what Excel displays: I wouldn't care if Excel refers to a date as week 52, 53 or even 54, and neither if it refers to dates in same 7-day period (week) as week 52/54 and week 1, it's just a reference, the very main date values doesn't change; I'd just would care about what I'll be doing with this data.

Just analyze this, review your redefined criteria (now you have a full 20 end-beginning of years data), tell me what you decide and I'll adjust the formulas.

All this supposing I hadn't make any mistake! Which it's very possible, even I checked everything very carefully. If this would be the case, I apologize and please let me know so as I can try to fix it asap.


Just let me know where I made the mistakes that I'm unable to find or otherwise what you decide or how do you want to do with your data, always considering what and how Excel treats the involved data.


Regards!


PS: I forgot to check your last comment on 22/23 week, I'm now doing that.
 
Hi, Faseeh!


Please check uploaded file mentioned in the previous post, considering it has comments for Jejo that might not apply to your questions. Just as a matter of economy, remember efficiency issue? :)


Regards!
 
Hi, Jejo!


My mistake while showing the week number for ending date, I left the 2nd. parameter of WEEKNUM as 2 (week starting on Monday but not for European system). Changing it to 21 (as I posted upwards for European system calculation) seems to be working fine. I send you again the updated file with equal values for the 7 examples you provided in all posts. Maybe the last one? I hope so.


http://dl.dropbox.com/u/60558749/Adding%20week%20numbers%20in%20different%20years%20%28for%20Jejo%20at%20chandoo.org%29.xlsx


Regards!
 
You must log in or register to reply here.
Back
Top