0

I am creating a Google Sheet for teachers that automatically fills in the week number beginning with the first week of school in early Sept. and continuing to the last week of school in mid June. I am currently using WEEKNUM, which is working well, but I am anticipating that this will become inaccurate after vacation weeks. I want to exclude these vacation weeks from the count, but WEEKNUM will include them.

0

You will need to arrange things in your sheet to accommodate this.

You haven't shared your sheet so difficult to be specific but if you were to include in the sheet the dates on which terms begin (and perhaps end) then you could include the week number that way ie `=WEEKNUM(date, [type])

Reference

| improve this answer | |
0

See testSheet

You can put Week count start date in cell B1. Then you can specify vacation start and end.

Idea is to calculate weeks omitting vacations.

For that purpose we use NETWORKDAYS.INTL() formula and since it will return working days we wrap it with QUOTIENT() formula with divisor of 5.

NETWORKDAYS.INTL counts days between dates and excludes holidays, so here comes vacation start and end.

We use SEQUENCE() formula to generate dates based on vacation start and end. You may add more vacations adding start and end dates and adding SEQUENCE() formula to array {}.

=QUOTIENT(
 NETWORKDAYS.INTL(
  $B$1,
  TODAY(),
  1,
  {
   SEQUENCE($B$3-$B$2+1,1,$B$2,1);
   SEQUENCE($B$5-$B$4+1,1,$B$4,1)
  }
  ),
 5)+1
| improve this answer | |

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.