Forum Discussion
Weeknum count up only every 2 weeks
Previously, I asked a question on whether it was possible to calculate weeks on Tuesday and also to allow counting up till the next year. and this is the code I was generously given from a user from this platform 🙂 [I'm not sure if I should/can tag people so you can check who it is from the recent question I asked]
=WEEKNUM(A6,12)+IF(YEAR(A6)=2022,60,112)
basically it starts from 100 and every week after that on Tuesday it adds by 1, but I realized I needed it to only add 1 every 2 weeks not every 1 week which I didn't ask in the previous question.
and is there a way to also keep track of these weeks like Section 1 as the first week and Section 2 as 2nd week and have it reset every to weeks.
I assume we need to encase it in an "IF" clause to do this but I cant think of how to use it.
so two items I need to understand how to do:
1. Count up only every two weeks
2. also have an indicator that this is the 1st section of the week and next is 2nd section and it resets after every 2 weeks
The QUOTIENT function performs integer division. QUOTIENT(A6-43431,14) is equivalent to INT((A6-43431)/14).
A6-43431 is the number of days since 27-Nov-2018.
QUOTIENT(A6-43431,14) returns the whole number of 14-day periods (i.e. 2 week periods) between 27-Nov-2018 and the data in A6. The date 27-Nov-2018 was chosen so that the result is exactly 100 for Tue 27-Sep-2022. Every 2 weeks, the number will increase by 1.
6 Replies
i changed it to a table but essentially it is the same.
=IF(ISODD(WEEKNUM([@Column2],12)+IF(YEAR([@Column2])=2022,60,112)), "Section 1 "&WEEKNUM([@Column2],12)+IF(YEAR([@Column2])=2022,60,112),"Section 2 "&WEEKNUM([@Column2],12)+IF(YEAR([@Column2])=2022,59,111))
Column 2 is the date column. but now the issue is that it gives me something like that. it shows section 1 and 2 for the first week correctly but it expectly jumps 2 weeks in the next one but that is not what i wanted.
Section 1 103
Section 1 103
Section 1 103
Section 1 103
Section 1 103
Section 2 103
Section 2 103
Section 2 103
Section 2 103
Section 2 103
Section 2 103
Section 2 103
Section 1 105
Section 1 105
Section 1 105
Section 1 105
Section 1 105
Section 1 105
Section 1 105
Section 2 105
Section 2 105
Section 2 105
Section 2 105
Section 2 105
Section 2 105
Section 2 105
Section 1 107
Section 1 107
it should be Section 1 103 then Section 2 103, then Section 1 104 then Section 2 104, Section 1 105... so on.How about
="Section "&(ISODD(WEEKNUM(A6,12))+1)&" ""IENT(A6-43431,14)
or
="Section "&(ISODD(WEEKNUM([@Column2],12))+1)&" ""IENT([@Column2]-43431,14)
43431 is the value of 27-Nov-2018.
- oh that works beautifully, can you please elaborate why you used quotient? i dont understand how that works in this context
