Utilities.formatDate returning wrong week number

Question

In Google Script I have the following code:

var myDate = new Date(sheet.getRange(3,1).getValue());
var year = Utilities.formatDate(myDate, "Europe/Amsterdam", 'dd-MM-yyyy');
var weekyear = Utilities.formatDate(myDate, "Europe/Amsterdam", 'dd-MM-YYYY');
var week = Utilities.formatDate(myDate, "Europe/Amsterdam", 'w');

When I insert 30-12-2015 as date, the result will be.

// year: 30-12-2015
// weekyear: 30-12-201**6**
// week: 1

In my local timezone, it should be week number 53. Not 1. If I calculate the week in ISO 8601, the result is 52.

Strange, isn't it?

Edited: New attempt, with this code

var cursus_datum = sheet.getRange(3,1).getValue();
  Logger.log('type of data: ' + typeof cursus_datum);
  Logger.log(cursus_datum);
  Logger.log(Utilities.formatDate(cursus_datum, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "d-MM-y HH:mm"));
  Logger.log(Utilities.formatDate(cursus_datum, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "w"));

This results in

// type of data: object
// Wed Dec 30 09:00:00 GMT+01:00 2015
// 30-12-2015 00:00 which is perfect
// 1 which is **not** correct.

The wrong week is the bug.

I am also having this problem in 2021.. and wondering how it hasn't happened before as it's in a script running for months without change.. — ACV, Dec 29 '21 at 19:31

score 2 · Accepted Answer · answered Dec 12 '15 at 20:35

2

The Utilities.formatDate documentation states that it uses Java SimpleDateFormat - I have a feeling this might be the cause of the problem as they are Locale sensitive and I would take a guess it will be using the default US locale (I don't think passing in the timezone changes the locale). The problem is the US locale calendar has getFirstDayOfWeek() = 1 (SUNDAY) and getMinimalDaysInFirstWeek() = 1. And for ISO 8601 you need the settings 2 and 4 respectively. I think you're better off sticking to the Javascript code you linked to if you want to work with week years.

answered Dec 12 '15 at 20:35

DDD

1,462
15
19

1

If I use the javascript code I linked to, I get week 52 as result, while it should be 53. Strange nobody else seems to have this bug. – Peter Driessen Dec 16 '15 at 10:51
It should do. Next you can simply copy in those extra Date methods to your own Google Apps Script .gs file (put them at the top level, not inside any other functions) and use them as in the fiddle. – DDD Dec 16 '15 at 16:38
Yes it does. Great thanks, don't know how I got 52 as a result then. – Peter Driessen Dec 19 '15 at 11:59

score 1 · Answer 2 · answered Dec 10 '15 at 18:13

new Date() will accept a few different parameter configurations, but you still need to make sure that the parameters are correct. You can't just plug anything into new Date() If the variable is already a date type, then there is no point using new Date(). You can test for the data type with typeof.

var dateFromSheet = sheet.getRange(3,1).getValue();
Logger.log('type of data: ' + typeof dateFromSheet);

If the data type is a string, it must be in a valid date string format. There are a few different formats. But, again, you need to use a valid format.

ISO 8601 syntax "YYYY-MM-DD" or "YYYY-MM" or "YYYY" or "YYYY-MM-DDTHH:MM:SS"
Long Date syntax - Year, month and day can be in any order: "Mar 7 2015" or "2015 Mar 7", and Month can be written in full: "2015 March 7"
Short dates - "/" or "-" can be used. "MM/DD/YYYY" or "YYYY/MM/DD" NOTE: You can not use "DD/MM/YYYY"
Full Date Format - "Wed Mar DD YYYY HH:MM:SS GMT+0100 (W. Europe Standard Time)"

I changed my post, and added your approach to it. However, I'm not having problems with day, month or year anymore. But the week is still a problem. And indeed, do NOT use YYYY, because then you get the year related to the week. And because the week is wrong, you get the wrong year. — Peter Driessen, Dec 11 '15 at 07:52

score 0 · Answer 3 · answered Feb 28 '22 at 20:00

0

This is what I did:

function getCurrentCalendarWeek(){
  let date = new Date();
  let weekNum = Utilities.formatDate(date, "Europe/Berlin", "w") - 1;
  if (weekNum === 0){
    weekNum = 1
  }
  return weekNum
}

answered Feb 28 '22 at 20:00

Shezan Kazi

4,471
3
14
27

Utilities.formatDate returning wrong week number

3 Answers3

Linked

Related