Year-Week Number

[adam_neb]

I am using Barry Houdini's Week Number Formula:

=INT((WEEKDAY(DATE(YEAR(B4011-WEEKDAY(B4011+1,3)+3),1,-3))+B4011-DATE(YEAR(B4011-WEEKDAY(B4011+1,3)+3),1,-2))/7)

The formula returns the Week Number in the format I need. However, I need the output to be YYYY-WW where WW is the Week Number.

Using 12/31/2006 as an example, Barry's formua gives week number 1. This would be Year 2007 Week 1. Does anyone have an idea of how to get the Year of the corresponding week (when the year of the Week Number does not match the Year of the Date)?

 

[ryal001]

Week numbers can be very confusing. If you are using January 1 as your starting point then 31 Dec 2006 is the 53rd week. Use the following formula (borrowed from http://www.cpearson.com/excel/weeknum.htm):

=TRUNC(((G11-DATE(YEAR(G11),1,0))+6)/7)

 

[barry houdini]

I am using Barry Houdini's Week Number Formula:

=INT((WEEKDAY(DATE(YEAR(B4011-WEEKDAY(B4011+1,3)+3),1,-3))+B4011-DATE(YEAR(B4011-WEEKDAY(B4011+1,3)+3),1,-2))/7)

The formula returns the Week Number in the format I need. However, I need the output to be YYYY-WW where WW is the Week Number.

Using 12/31/2006 as an example, Barry's formua gives week number 1. This would be Year 2007 Week 1. Does anyone have an idea of how to get the Year of the corresponding week (when the year of the Week Number does not match the Year of the Date)?

I think I simplified that formula very slightly, to get the correct year and the week number try

=YEAR(B4011+4-WEEKDAY(B4011))&"-"&INT((WEEKDAY(DATE(YEAR(B4011-WEEKDAY(B4011)+4),1,4))+B4011-DATE(YEAR(B4011-WEEKDAY(B4011)+4),1,-2))/7)

 

[adam_neb]

Barry - Wow! Thanks! That gave me the year that I needed (I started changing the off-years manyally over the weekend. I entered your formula and coped it down and it is exaclty what I was hoping for!

One formatting question. I have been trying to modify the formula to give the output of YYYY-WW (two digit weeks, so 2006-01 rather than 2006-1). I have tried using

text(yourformula,"000-00")

to change the output but it did not give me the format I'd like. Any suggestions on that?

 

[barry houdini]

Hi,

I was looking at the week number formula for another thread and managed to simplify it a little (again). Combined with your request for a two digit week number.....try this formula for a date in A1

=YEAR(A1-WEEKDAY(A1)+4)&TEXT(INT((A1-WEEKDAY(A1)-DATE(YEAR(A1-WEEKDAY(A1)+4),1,4))/7)+2,"-00")

 

[adam_neb]

Barry:

Thank you so much for all of your time and hard work helping me with this formula. I have used several, but none quite fit, and I could not get a formula to work as I hoped. Yours works exactly as I wished.

Now I will have to spend some time looking at your formula and learning what each part does.

Much appreciated,

Adam