0

I like to find the beginning of the next month. The beginning on the current month (1st of March) is stored in a Google spreadsheet at B1" but my code returns me 29th of March if I add another month.

The cell B1 holds the value 01.03.2021. Screenshot of the sheet with cell B1

function date_next_recort(){
  ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet = ss.getActiveSheet();
  var timezone = ss.getSpreadsheetTimeZone();
  var date = new Date(sheet.getRange("B1").getValue());
  var record_next = new Date(date);
  var month = record_next.getMonth();
  record_next.setMonth(month + 1);
  Logger.log("ScriptTimeZone: " + Session.getScriptTimeZone());
  Logger.log("SheetTimeZone:  " + ss.getSpreadsheetTimeZone()); 
  Logger.log('record_start: ' + Utilities.formatDate(date, timezone, 'MMMM dd, yyyy'));
  Logger.log('record_next:  ' + Utilities.formatDate(record_next, timezone, 'MMMM dd, yyyy'));
  return record_next;
}

output:

2:00:49 PM Info ScriptTimeZone: America/New_York
2:00:49 PM Info SheetTimeZone:  Europe/Vienna
2:00:49 PM Info record_start: March 01, 2021
2:00:49 PM Info record_next:  March 29, 2021
Max
  • 340
  • 2
  • 15
  • 1
    Welcome to [so]. Does B1 has a date or a text value? What is shown in the formula bar? What is the spreadsheet time zone? What is the time zone of your script? Are you using the old IDE or the new IDE? – Rubén Apr 01 '21 at 21:26
  • I've tried replicating your script on my end and it seems to return the expected value. Can you share a copy of your sheet? – Jason E. Apr 01 '21 at 21:39
  • 2
    I thought that @Rubén 's comment might lead to the solution. For example, is this thread related to your current issue? https://stackoverflow.com/q/66194990 – Tanaike Apr 02 '21 at 01:54
  • I modified my code and some details and a screenshot. @Rubén The cell B1 holds the value `01.03.2021`. My script shows now also the script (New York) and sheet (Vienna) timezone. – Max Apr 03 '21 at 12:06
  • Please see @Tanaike's comment with the link of the possible solution to your concern. Also, please confirm if that solves the issue you encountered. – Jason E. Apr 05 '21 at 16:50

0 Answers0