Forum Discussion
Solved
Count up from a certain number weeks which carry on to next year
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
calculates the week starting from Tuesday
Starts the count from 100 onwards from this week
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
Doesn't start the week on Tuesday.
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?
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)
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)
- AH this works beautifully! thanks a lot!
