calculating with dates and for loop

Question

I am trying to make a for loop with dates but it isn't going as planned. What I am trying to do is input startdate in cell A1 and enddate in cell B1. Then a for loop which runs: if startdate is less then enddate, put date in cell A2 and proceed to next column. The only thing I am stuck with is how to add one day to a date.

See code below:

function test() 
{
  var app = SpreadsheetApp;
  var ss = app.getActive();
  var sheet = ss.getActiveSheet();
  
  var start = sheet.getRange("A1").getValue();
  var end = sheet.getRange("B1").getValue();
  var day = 3600*24*1000;
  var diff = Math.floor((end-start)/day);
  var column = 1;
  
  for(var i=0;i<=diff;i++)
  {
    sheet.getRange(2, column).setValue(start+i*day);
    column++;
  }
  
}

Can someone explain to me why it doesn't work. Thanks a lot

See [*How can I add 1 day to current date?*](https://stackoverflow.com/questions/9989382/how-can-i-add-1-day-to-current-date) Note that not all days are 24 hours long where daylight saving is observed, they are shorter and longer by the DST offset (typically 1 hour but might be 30 minutes). — RobG, Sep 25 '18 at 07:16
Thanks I get how to add one day but how do i set the new date because if I try this in the for loop then you only set the day not the month and year. — Jens Slofstra, Sep 25 '18 at 07:48

score 0 · Answer 1 · answered Sep 25 '18 at 09:06

Assuming start is a Date, then your issue is:

start + i * day

Where the + operator is used, the left operand (the Date) is an object so is reduced to a primitive with hint string, so the + is treated as string concatenation, not a number.

You can try:

+start + i * day

to coerce start to a number, but then the result will be a number, not a Date. Also it doesn't deal with the length of day issue. I suggest you first copy the start date so you don't modify it, then increment the copy per the duplicate:

function test() {
  var app = SpreadsheetApp;
  var ss = app.getActive();
  var sheet = ss.getActiveSheet();

  var start = sheet.getRange("A1").getValue();
  start = new Date(+start);
  var end = sheet.getRange("B1").getValue();

  var column = 1;

  for (var i=0; start <= end; i++) {
    sheet.getRange(2, column++).setValue(new Date(start.setDate(start.getDate() + 1)));
  }
}

calculating with dates and for loop

1 Answers1