Forum Discussion
Weeknum count up only 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.
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
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.
that is amazing! so if i were to ever need it to count less or more, i could modify the 43431 number i assume? like adding 1 or minus 1 maybe?
it still is a little complicated (to me at least) but i'll go look up more on quotient to understand it more.Thanks again for the help and reply!