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.

2 Answers 2

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
2
  • Thank you! This is a great solution. Do I need to add additional SEQUENCE formulas for additional vacation weeks? If so, is there a way to make this dynamic so I don't need to adjust the formula if there are a different number of vacation weeks in a year? Dec 9, 2020 at 15:14
  • yes, unfortunately you need to adjust formula. I'll try to think about other solution
    – Oleg_S
    Dec 10, 2020 at 10:12
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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