google sheets - App script returns current week as 3 while it's week 2

Question

To get the current week I have this piece of code:

 var today = new Date();
  var week = Utilities.formatDate(today, "Europe/Amsterdam", "w"); 

This returns today as Tue Jan 12 2021 11:30:42 which is correct, as I am in Amsterdam. But it returns the current week as 3 while it's week 2. Looking back into last weeks logs, it returned week 2 while it was week 1. And the week before that, it returned week 53 which was correct. So this worked fine until the new year and it completely skipped week 1. Any ideas on how to fix this?

Answer 1

Explanation:

I don't think there is an actual issue.

The confusion comes from the fact that week 1 started last year.

If you console.log all the dates starting from 26th of December 2020 until 12th of January 2021:

function myFunction() {
  const December = [26,27,28,29,30,31];
  const January =[1,2,3,4,5,6,7,8,9,10,11,12,13];
  December.
  forEach(n=>{console.log(Utilities.formatDate(new Date(2020,11,n), "Europe/Amsterdam", "yyyy-MM-dd"),"Week: ",
Utilities.formatDate(new Date(2020,11,n), "Europe/Amsterdam", "w"))});
  January.
  forEach(n=>{console.log(Utilities.formatDate(new Date(2021,0,n), "Europe/Amsterdam", "yyyy-MM-dd"),"Week: ",
  Utilities.formatDate(new Date(2021,0,n), "Europe/Amsterdam", "w"))});
}

you will get these results:

2020-12-26 Week:  52
2020-12-27 Week:  1 // Sunday
2020-12-28 Week:  1
2020-12-29 Week:  1
2020-12-30 Week:  1
2020-12-31 Week:  1
2021-01-01 Week:  1
2021-01-02 Week:  1
2021-01-03 Week:  2 // Sunday
2021-01-04 Week:  2
2021-01-05 Week:  2
2021-01-06 Week:  2
2021-01-07 Week:  2
2021-01-08 Week:  2
2021-01-09 Week:  2
2021-01-10 Week:  3 // Sunday
2021-01-11 Week:  3
2021-01-12 Week:  3
2021-01-13 Week:  3

Essentially, the 1st week started last year on 2020-12-27 the 2nd week on 2021-01-03 and the 3rd week on 2021-01-10, therefore the current week according to that logic is 3 which is the number you are getting.

Workaround:

If you want to adjust this logic you can deduct 1 from the current week number so you will get 2 as the current number of week:

 var today = new Date();
 var week = Utilities.formatDate(today, "Europe/Amsterdam", "w") - 1;

Or you can use this answer and adjust the week number:

function exampleFunction() {
    Date.prototype.getWeek = function() {
    var onejan = new Date(this.getFullYear(),0,1);
    return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7)-1;
    } 
    var now = new Date();
    Logger.log(now.getWeek());
}

The only issue is that the first week of next year (which might be this year as well) will have 0 week number. But you can adjust this with an if condition to make sure it won't happen.