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)

Sheet 'Holidays' contains a list of holidays in the column B starting from row 2
Variable date is the date for which I want to find out the index for
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:

The date2 calculation isn't working for some reason (var date2 = lastSundayDate.valueOf() + j*86400;)
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!

1. Where is lastSundayDate defined? 2. Looks like the rest of your code will break if lastSundayDate is not defined properly. Well, at least all the points that you talk about in your question... My suggestion is to show all of your code that encompasses the definitions of all your variables, and perhaps post the result of the Execution Transcript from the Script Editor, or an output of detailed Logging. — Chris Cirefice
– Chris Cirefice, Commented Nov 14, 2013 at 23:12
lastSundayDate is one of the arguments being passed to the function. It contains the date of the last Sunday, i.e., the Sunday that starts a week. I'll post the Execution script and the log. — user2991044
– user2991044, Commented Nov 15, 2013 at 5:56

Serge insas · Accepted Answer · 2013-11-16 20:59:26Z

2

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);
}

answered Nov 16, 2013 at 20:59

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Cirefice Over a year ago

This is actually a very useful prototype addition :)

Chris Cirefice Over a year ago

I think that my answer explains the source of his problem, but yours gives a very simple solution - one that he should definitely use considering the amount of work he is doing with Dates.

Chris Cirefice · Accepted Answer · 2013-11-16 19:18:45Z

0

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 :)

answered Nov 16, 2013 at 19:18

Chris Cirefice

5,8657 gold badges49 silver badges75 bronze badges

2 Comments

user2991044 Over a year ago

Thanks so much for the detailed answer, but I figured out the solution. There were two problem areas: dates being passed to the function were strings and not dates, so I created date objects and passed these strings to convert into proper date formats. Secondly, the valueOf function gives exact number of milliseconds since 1/1/1970, so to add a day to a date, all I had to do was add 24*3600*1000 milliseconds to the date being passed. Now, the program is running flawlessly :)

Chris Cirefice Over a year ago

So was what I said in my answer correct? My original assumption was that lastSundayDate was not being passed around correctly (and it was a string as you said and note a Date like it should have been). My second notice was that valueOf() you were calling on your Date, which wasn't the operation that you wanted. @sergeinsas also gave you a useful function that you could use in your scripts as well. So I think it's appropriate to a least mark one of our answers as correct :)

Collectives™ on Stack Overflow

Working with Dates in Google Apps Script

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related