Hello,
I am trying to create a formula that, based on the year, will generate a weeknumber between 30 and 32. The pattern should be 30,30,31,32,32,31,30,30,31..etc
Is there a formula for this?
Hello,
I am trying to create a formula that, based on the year, will generate a weeknumber between 30 and 32. The pattern should be 30,30,31,32,32,31,30,30,31..etc
Is there a formula for this?
Which year do you start from ?
The startyear shouldn't matter I think.
But
2019 32
2020 31
2021 30
2022 30
2023 31
etc...
Why wouldn't the start year matter? How should one know where in the sequence to start?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Because I think the heart of the problem lies in that B only comes once in between a double occurrence of A and C (AA B CC B AA). I have no clue how to create such a sequence (and neither does the Fill-function in excel ;-) )
So let's assume A1 holds the year, B1 should display the weeknumber. If A1 changes, so does B1.
Am I making sense? Sorry if this comes a bit messy...
But you said this:
based on the year,
Yes I did, but I assume the best way to go about this is to doFormula:Please Login or Register to view this content.
So the sequence would be
AA B CC B AA B CC B AA B CC B, Etc. ?
This formula is independent of the year:
=IF(MOD(ROWS($1:1),3)=0,1,MOD(INT((ROWS($1:1)-1)/3),2)*2)+30
it returns the sequence starting with 30, i.e. 30 30 31 32 32 31 30 30 etc.
Hope this helps.
Pete
I don't see why the year is relevant at all, sorry. I'll leave this to someone who gets it.
That's helping a lot. Thanks!
How should I link that to a year?
Attachment 709400
The attachment is invalid. Please follow the directions in the yellow banner at the top of the screen.
Pete
Pete - it just shows a cell with a spinner selector with 2019 currently selected and 31 (from your formula) in the adjacent cell.
Here's the requested workbook
Which year starts the sequence?
There are 6 possible outcomes in your sequence, so if you take your year value (in A1) and subtract a fixed year which starts the sequence, and then take the remainder of that after dividing by 6, you will have a value that should be added on to the ROWS($1:1) term (twice) in the formula that I gave you, i.e. in B1:
=IF(MOD(ROWS($1:1)+MOD(A1-start_year,6),3)=0,1,MOD(INT((ROWS($1:1)+MOD(A1-start_year,6)-1)/3),2)*2)+30
Hope this helps.
Pete
This is awesome!!! Thanks so much!
Actually, you don't need the ROWS($1:1) terms - I thought you would be copying the formula down a column. You can use this instead in B1:
=IF(MOD(MOD(A1-2016,6)+1,3)=0,1,MOD(INT((MOD(A1-2016,6))/3),2)*2)+30
This starts the sequence at the first 30 in 2016, so adjust this if required.
Hope this helps.
Pete
P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.
You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks