Working with Dates in Google Apps Script

Question

What I am trying to do here is this - I want to give index to only the workdays in each week.

So, if in a week, Monday and Wednesday are holidays, then Tuesday should get 1, Thursday should get 2, Friday should get the index 3. Otherwise, in a normal week without any holidays, Monday should get 1, Tuesday 2, Wednesday 3, and so on ...

Here is the code I have written (I haven't coded in years now, so please pardon the crude approach)

1. Sheet 'Holidays' contains a list of holidays in the column B starting from row 2

2. Variable date is the date for which I want to find out the index for

3. Variable dayOfTheWeek is the number of day of 'date' counted from last Sunday, so if date is a Monday, dayOfTheWeek is 1; if date is Tuesday, dayOfTheWeek is 2, and so on ...

function indexOfWorkdayOfTheWeek (date, dayOfTheWeek, lastSundayDate)
{
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = activeSheet.getActiveRange();
  var activeRow = activeCell.getRowIndex();
  var activeColumn = activeCell.getColumn();

  var count = 1;

  for (var j = 1; j < dayOfTheWeek; j++) 
  {
    var date2 = lastSundayDate.valueOf() + j*86400;

    Logger.log('Date ' + j + ' is:' + date2);
    Logger.log('Last Sunday is:' + lastSundayDate);

    if (holidayOrNot(date2) == true) 
    {
    }
    else
    {
        count = count + 1;
    }
  }

  return count;
}

function holidayOrNot(date2)
{
  var holidaysSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Holidays');
  var listOfHolidays = holidaysSheet.getSheetValues(2, 2, 95, 1);      
  var isDateMatch = false;

  for (var k = 0; k < 90; k++) 
  {
    if (date2 == listOfHolidays[k].valueOf()) 
    {
      isDateMatch = true;
      break;
    }       
    else
    {
      continue;
    }
  }

  return isDateMatch;
}

I think the problem is two-fold here:

1. The date2 calculation isn't working for some reason (var date2 = lastSundayDate.valueOf() + j*86400;)

2. The function holidayOrNot is returning false, no matter what, even if it encounters a holiday ... the condition date2 == listOfHolidays[k] isn't working for some reason...

Help would be appreciated!

Answer 1

maybe this method below could help you in your calculations, it returns an integer corresponding to the day of the year so if you apply this to your holidays days and compare to the days of interest it could be a good way to find matches.

here it is, just add these lines outside of any function in your script (so you can use it anywhere) then use it like this :

var d = new Date().getDOY();
Logger.log(d)

Here the method :

Date.prototype.getDOY = function() {
var onejan = new Date(this.getFullYear(),0,1);
return Math.ceil((this - onejan) / 86400000);
}

Answer 2

Assuming that lastSundayDate is being passed around correctly, I see a glaring problem:

lastSundayDate.valueOf().

valueOf() on Date objects returns the primitive value... it looks like you're going for adding a day to the date (86400 seconds * j)? I can't tell what the logic is supposed to be here. But the valueOf() date2 is definitely giving you an integer something like: 1384628769399 (see here).

What you really want to accomplish is something like Date.getDay(), or something similar so that you can add hours, days, etc. to the original Date. This is likely the source of all your problems.

What you can do is read the Mozilla Developer Network documentation on Date objects to see all of the functions on Dates and their uses. You can greatly simplify what you're trying to do by using these functions, instead of doing abstract operations like j * 86400.

It should also be noted that you can do simple operations such as the following, to add 4 hours to the current Date (time):

var myDate = new Date();
Logger.log(myDate); // ~ console.write
var laterDate = new Date(myDate.setHours(myDate.getHours() + 4));
Logger.log(laterDate); // ~ console.write

which gives the following:

[13-11-16 14:13:38:947 EST] Sat Nov 16 14:13:38 GMT-05:00 2013
[13-11-16 14:13:38:954 EST] Sat Nov 16 18:13:38 GMT-05:00 2013

Working with dates can be tricky - but it's always best to use the simplest methods that are available, which are built into the Date objects themselves. There are also numerous other libraries that provide extended functionality for Dates such as Date js.

If you're still running into your problem after attempting to try using methods I displayed above, please run your script and post both the Execution Transcript and the content of the Logger so that I can help you narrow down the issue :)