Subtract 1 week from a special dateformat

I got a funny problem with a part of my macro.

Basically I got a big datafile with a lot of columns with data. In columns "O" (15) there is a "date" in the following format; yyyyww, for example 201648 which is the year 2016 in the week 48. In column "AB" (28) there is another column where I in my code filling the rows with some data. If column "O" is the start week of an event then column "AB" is the start week of the preparation for the event which always will be 1 week earlier than the start week of event (Column O). Therefore my code was taking the "date" in column "O" minus 1 week which will be correct most of the times. 201648 - 1 = 201647. The problem is that if for example the start week in column "O" is 201701 then my date in column "AB" will be 201700 which obviously is wrong. Instead it should give me 201652 since year 2016 is a total of 52 weeks. Is there a way to work around this?

Please Login or Register  to view this content.

How about:

Please Login or Register  to view this content.

Thx for both of your the answers. Your code is a upgrade from my code yet but the thing is that one week earlier of 201501 might be 201453 since a year can both have 52 and 53 weeks if I am correct. Do you see the problem?

Here, try this:

Please Login or Register  to view this content.

That looks a lot better! But shouldn't the code be the following? Since it will have to take the last weeknumber of the previous year? Om maybe I am wrong.

Please Login or Register  to view this content.

And another thing, tell me if I am wrong but according to my calendar 2016 only has 52 weeks. But If I run the formula I get 201653 from 201701.
Its the same in excel if I have the following formula: =WEEKNUM(DATE(2016;12;31);2) and it gives me 53. I know that =WEEKNUM has different return types but 2 is start on Monday so I am not sure why it give me week 53.

There are two systems used for this function:

• System 1 The week containing January 1 is the first week of the year, and is numbered week 1.
• System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.