Year-Week Number

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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)?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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)
 
Upvote 0
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?
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,465
Messages
6,178,821
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top