CODEWITHSUNDEEP
javascriptgoogle-apps-script
allanquartz
allanquartz

Reputation: 97

setDate() accumulating days unexpectedly

Using Google Sheets. I'm trying to check for data entries within a fortnight range from a changed date in the sheet. So I need a variable date that is set 1, 2, 3.... 14 days from the entered date.

var startFortnightDate = ss.getSheetByName('FortnightValidation').getRange('C3').setValue("=to_date("+e.value+")").getValue();
var variableDate = startFortnightDate;
//Loop start
for (var dayCount = 1; dayCount < 8; dayCount++) {//each day of the week
  for (var weekCount = 0; weekCount < 2; weekCount++) {//each week of the fortnight
variableDate = startFortnightDate;//this is to reset the date to allow setting of the date to variable number of days forward
variableDate.setDate(variableDate.getDate() + (dayCount + (7*weekCount)));//This changes the date and accumulates if it isn't reset????
Logger.log("Line 197. variableDate = " + variableDate);
Logger.log("Line 199 " + startFortnightDate);
}
}
//Loop end

For reasons that I cannot fathom, it seems that fortnightStartDate is accumulating as well variableDate, even though I am resetting with each loop. Is something other than the value of of startFortnightDate being assigned to variableDate?

Part of the log is below:

[16-08-08 16:01:43:609 AEST] Line 199 Mon Feb 13 2389 00:00:00 GMT+1100 (AEDT)
[16-08-08 16:01:43:609 AEST] Line 197. variableDate = Mon Feb 27 2389 00:00:00 GMT+1100 (AEDT)
[16-08-08 16:01:43:610 AEST] Line 199 Mon Feb 27 2389 00:00:00 GMT+1100 (AEDT)
[16-08-08 16:01:43:610 AEST] Line 197. variableDate = Tue Feb 28 2389 00:00:00 GMT+1100 (AEDT)
[16-08-08 16:01:43:611 AEST] Line 199 Tue Feb 28 2389 00:00:00 GMT+1100 (AEDT)
[16-08-08 16:01:43:611 AEST] Line 197. variableDate = Wed Mar 08 2389 00:00:00 GMT+1000 (AEST)
[16-08-08 16:01:43:611 AEST] Line 199 Wed Mar 08 2389 00:00:00 GMT+1000 (AEST)
[16-08-08 16:01:43:612 AEST] Line 197. variableDate = Fri Mar 10 2389 00:00:00 GMT+1000

Upvotes: 0

Views: 59

Answers (2)

some1
some1

Reputation: 867

Instead of

var variableDate = startFortnightDate;

it should be

var variableDate = new Date(startFortnightDate);

That will fix the problem.

Upvotes: 1

Tiffany G. Wilson
Tiffany G. Wilson

Reputation: 573

You might have a pointer issue, but I'm not sure. Here is what worked for me:

var startFortnightDate = ss.getSheetByName('FortnightValidation').getRange('C3').setValue("=to_date("+e.value+")").getValue(); 
  //var startFortnightDate = new Date();  // Used for testing since I don't have your spreadsheet
  startFortnightDate = Date.parse(startFortnightDate); // Convert date object to milliseconds 
  var variableDate = startFortnightDate;
  var inc = 24*60*60*1000;               // One day in milliseconds
  for (var weekCount = 0; weekCount<2; weekCount++){
    for (var dayCount = 1; dayCount<8; dayCount++){
      variableDate += inc;              // Add one day
      Logger.log('startFortnightDate = %s',new Date(startFortnightDate))
      Logger.log('variableDate = %s',new Date(variableDate))
    }
  }

You'll notice that I switched the order of your loops. The way you have it, you increase by two weeks then by one day instead of increasing one day for two weeks.

I also simplified it a bit by converting to milliseconds using Date.parse(dateObject), which to me is an easier way to manipulate dates. Then on each loop variableDate increases by one day but startFortnightDate stays as-is. For display, convert back to the date object using new Date(milliseconds). The w3schools reference for date objects is very thorough and has helped me many times:

http://www.w3schools.com/jsref/jsref_obj_date.asp

Here's what a portion of my log looks like:

[16-08-08 03:00:08:361 EDT] startFortnightDate = Mon Aug 08 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:362 EDT] variableDate = Tue Aug 09 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:363 EDT] startFortnightDate = Mon Aug 08 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:363 EDT] variableDate = Wed Aug 10 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:364 EDT] startFortnightDate = Mon Aug 08 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:365 EDT] variableDate = Thu Aug 11 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:365 EDT] startFortnightDate = Mon Aug 08 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:366 EDT] variableDate = Fri Aug 12 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:367 EDT] startFortnightDate = Mon Aug 08 03:00:08 GMT-04:00 2016
[16-08-08 03:00:08:367 EDT] variableDate = Sat Aug 13 03:00:08 GMT-04:00 2016

Upvotes: 2

Related Questions