2

Basically, I just want to automatically increase the week number (on column A), and have it correspond with every new Monday (in Column B). I have been trying to click and drag, hoping it would somehow automatically adjust the increment every 7 rows but it doesn't seem to work.

How would I be able to do this without having to manually increase the number every Monday?

1

Formula

=ArrayFormula(IF(WEEKDAY(FILTER(C:C,C:C>0),2)=1,WEEKNUM(C:C,1),))

Explanation

WEEKNUM(C:C,1) returns the number of the week for all the values column C. To to fill only the cells that corresponds to mondays and that row is not empty, it was included as the TRUE result of an IF function with the following test:

WEEKDAY(FILTER(C:C,C:C>0),2)=1

ArrayFormula makes that the results be expanded to the required rows.

1
  1. Write a 0 on cell A1
  2. Insert the following formula on cell A2 =A1+IF(WEEKDAY(C2,2)=1,1,0)
  3. Copy cell A2 and paste on desired rows below
0

A generic counter (does not rely on any other column) for ColumnA for example:

=if((rows($1:1)-1)/7=int((rows($1:1)-1)/7),max(A$1:A1)+1,"")

to, when copied down, place an incrementing integer every eighth row without displaying in intervening cells. Assumes a label (or a blank cell) in A1, but a value could be entered there so that the incrementing starts from it.

Change the 7s to say 12s for example and the values would be spaced a further five rows apart.

Can be placed in any row and copied down but rows above should be blank or text and the row number in A1 adjusted to the row number immediately above that chosen for the formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.