Count up from a certain number weeks which carry on to next year

[Raskyl]

Hello, I want to understand if there is a way to count weeks starting from Tuesday.

So the general gist of it is that I want to make a counter. it has already reached 100 weeks now and I'm trying to see if I can make a formula which

1. calculates the week starting from Tuesday

2. Starts the count from 100 onwards from this week

3. when it flows to the next year, it wont reset back to week 1

This is the formula I'm trying to use, though I know that this is not exactly what i need

WEEKNUM(A6)+IF(YEAR(A6)=YEAR("2022"),"60","112")

the issue is that

1. Doesn't start the week on Tuesday.

2. The function I think I made some mistake as it is showing false but it should be correct as I'm just extracting the year

   • WEEKNUM(A6)+IF(YEAR(A6)="2022","60","112")

   • Tried this too but didn't work.

May i know what formula i might need to use?

i would guess some form of If the week extracted is Monday then -1 else 0 or something like that?

[#2 · Accepted solution]

Hi @Raskyl 

the WEEKNUM function has a second parameter where you can control the day a week should start with. If you use 12 as the second parameter, the week starts with Tuesday.

And you should not use strings in your IF-statement, because the YEAR function does not return a string but a number.

Try this one instead: 

=WEEKNUM(A6,12)+IF(YEAR(A6)=2022,60,112)

[#3]

AH this works beautifully! thanks a lot!