CODEWITHSUNDEEP
javascriptdatedatetimegoogle-apps-scriptgoogle-sheets
John Galassi
John Galassi

Reputation: 318

Utilities.formatDate starts the week on day 7 instead of day 1

this is NOT a duplicate
According to its documentation, Utilities.formatDate(date, timeZone, format) works according to Java SE SimpleDateFormat class specifications.
Which, as you can easily tell, expect the week to start on Monday, that is Day 1.
Why does my week starts on sunday instead, even if it is clearly output as day 7?

screenshot

function testDate() {

  var ss         = SpreadsheetApp.getActiveSpreadsheet();
  var sheet      = ss.getSheetByName('history');
  var extension  = 2;
  var arrayDates = sheet.getRange(3, 2,  extension).getValues();

//flattening the array
function flatten(tdArray){return [].concat.apply([], tdArray);}  
  var arrayDates = flatten(arrayDates);

//"extracting" weeks' numbers from the dates
  var arrayWeekNums  = arrayDates.map(function(el){return Utilities.formatDate(el, "CET", "uyww");});
// CET = Central Europe Time
// u   = day of the week;
// y   = year in 4 digits;
// ww  = week numbers in 2 digits

Logger.log(arrayWeekNums); 
Logger.log(arrayDates);  
}


//Logger.log(arrayWeekNums);
//  [7201803, 6201802]
//  7-2018-03  week day: 7  ;  year: 2018  ;  week number: 03;
//  6-2018-02  week day: 6  ;  year: 2018  ;  week number: 02; ??

//Logger.log(arrayDates); 
//[20-01-11 22:21:54:187 CET] [Sun Jan 14 00:00:01 GMT+01:00 2018, Sat Jan 13 23:59:59 GMT+01:00 2018]

I've also switched my project's global settings from US to any european Contry: nothing changes.
How can I change this?

edit, as per request:
1."impostazioni internazionali" aka spreadsheet timezone(File>spreadsheet settings>locale) at the moment "Italia", but even with "United States" it's the same outcome;

2.script timezone(File> project settings): "GMT +01:00 - Paris"

spreadsheet copy: https://docs.google.com/spreadsheets/d/1f8ep3Hczo1jbu7s2PTGsaqog8qkW-dc9MAeYNOEBRbw/edit?usp=sharing

Upvotes: 1

Views: 1146

Answers (1)

ADW
ADW

Reputation: 4247

I don't think this is an issue with the locale or time-zone settings.

I ran the script for different points of time and this is what I found.

It seems that the day-of-the-week (dotw) and the week-of-the-year (woty) numbers are not connected.

In any year, week-1 does not start on the 1st Monday of that year.

Dotw is strictly Mon = 1, Tue = 2, etc.

Woty is tricky.

It appears that a year will have 53 weeks as long as the 53rd week can be completed by 31st Dec.

Else the year will have 52 weeks. And the "week 1" of the subsequent year will start in December of the previous year.

For example, 2016 ended on the 53rd week. And 2017 started on "week 1":

Date           Doty*  Dotw Woty    
23-Dec-2016    358    5    52
24-Dec-2016    359    6    52
25-Dec-2016    360    7    53
26-Dec-2016    361    1    53
27-Dec-2016    362    2    53
28-Dec-2016    363    3    53
29-Dec-2016    364    4    53
30-Dec-2016    365    5    53
31-Dec-2016    366    6    53
01-Jan-2017    1      7    1
02-Jan-2017    2      1    1
03-Jan-2017    3      2    1
                                     *Day of the year

But in 2017, as the 53rd week could not be accommodated before year-end, 31-Dec-17 was in "week 1" of 2018:

Date          Doty    Dotw Woty    
29-Dec-2017    363    5    52
30-Dec-2017    364    6    52
31-Dec-2017    365    7    1
01-Jan-2018    1      1    1
02-Jan-2018    2      2    1
03-Jan-2018    3      3    1

Similarly for 2019-20, the 52nd week ended on 28-Dec-19. And "week 1" of 2020, started on 29-Dec-19:

Date           Doty   Dotw Woty    
27-Dec-2019    361    5    52
28-Dec-2019    362    6    52
29-Dec-2019    363    7    1
30-Dec-2019    364    1    1
31-Dec-2019    365    2    1
01-Jan-2020    1      3    1
02-Jan-2020    2      4    1
03-Jan-2020    3      5    1
04-Jan-2020    4      6    1

Edit

I tried several time periods including:

new Date(0) => Thu Jan 01, 1970

and 

new Date(0, 0, 0) => Sat Dec 30, 1899 (?!)

As far as I can tell, woty changes on a Sunday. But dotw resets to 1 on a Monday. And as multiples of 7, they keep to that cycle.

So the best explanation seems to be that the two are not connected except for their 7-day cycle offset by a day.

And that the start of a new week

  • in the context of weekdays cycles through Monday to Sunday with Monday = 1
  • in the context of week number in a year ( 1 through 52 or 53), cycles through Sunday to Monday with a new week number starting on Sunday.

Upvotes: 2

Related Questions