Rainnic in the Clouds
Working from scratch, following simplicity
X

How to extract night and holiday hours from your Calendar with Apps Script

Nicola Rainiero Mon, 08/05/2019 - 15:16

It's time to update my Google Apps Script that exports calendar events into a spreadsheet. I use it to comparison my worked hours with the month payroll, so putting all my shifts on Google Calendar I have in automatic a sheet with all the shifts data. I added how to extract night and holiday hours and count the worked days in a range of dates.

This is the third article on the topic and if you want to know more I suggest you to read the previous posts:

In the new article I highlight three features useful to:

  1. count night hours in the Calendar event, once defined the hours interval (default 22-6);
  2. count holiday hours in the Calendar event once defined an array with the list of your national holiday;
  3. count the worked days in the defined range of dates.
How to extract night and holiday hours from your Calendar with Apps Script

The added settings

In order to count the night and holiday hours it is necessary to define when the shift is nocturnal (in Italy from 10 pm to 6 am) and what days are considered holidays. Besides I wanted to give the option to show or not the columns relating to these counts row by row.

var night_timing = [22, 6]; // to set the range of night hours --> night_timing[0]
var night = 1; // for adding the night column on the final sheet on(1)/off(0)  
var feast = 1; // for adding the holiday column on the final sheet on(1)/off(0)
var feast_days = "\
DATE(YEAR(A1); 1; 1);\
DATE(YEAR(A1); 1; 6);\
DATE(YEAR(A1); 4; 1);\
DATE(YEAR(A1); 4; 2);\
DATE(YEAR(A1); 4; 25);\
DATE(YEAR(A1); 5; 1);\
DATE(YEAR(A1); 6; 2);\
DATE(YEAR(A1); 6; 13);\
DATE(YEAR(A1); 8; 15);\
DATE(YEAR(A1); 11; 1);\
DATE(YEAR(A1); 12; 8);\
DATE(YEAR(A1); 12; 25);\
DATE(YEAR(A1); 12; 26)\
"; // list of national holidays formatted in YEAR/MONTH/DAY

Hence the code reads start and finish time, makes calculations using the classical sheets formulas and prints the results line by line.

An example of a sheet generated by my script
An example of a sheet generated by my script

Night hours

The formula used is the following \( &=MOD(Di-Ci;1)*24-(Di<Ci)*(22-6)+MEDIAN(6;22;Di*24)-MEDIAN(Di*24;6;22))/24 \) and I created a variable to store the increment. The code is a little verbose:

    // Code to count worked night hours
    sheet.getRange(i,totalColumns+1).setFormula('=HOUR(VALUE((MOD((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))-(TIME(HOUR(C' +i+ ');MINUTE(C' +i+ ');0));1)*24'
                                                +'-((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))<(TIME(HOUR(C' +i+ ');MINUTE(C' +i+ ');0)))*('+night_timing[0]+'-'+night_timing[1]+')'
                                                +'+MEDIAN('+night_timing[1]+';'+night_timing[0]+';(TIME(HOUR(C' +i+ ');'
                                                +'MINUTE(C' +i+ ');0))*24)-MEDIAN((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))*24;'+night_timing[1]+';'+night_timing[0]+'))/24))+MINUTE(VALUE((MOD((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))'
                                                +'-(TIME(HOUR(D' +i+ ');MINUTE(C' +i+ ');0));1)*24-((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))<(TIME(HOUR(C' +i+ ');MINUTE(C' +i+ ');0)))*('+night_timing[0]+'-'+night_timing[1]+')'
                                                +'+MEDIAN('+night_timing[1]+';'+night_timing[0]+';(TIME(HOUR(C' +i+ ');'
                                                +'MINUTE(C' +i+ ');0))*24)-MEDIAN((TIME(HOUR(D' +i+ ');MINUTE(D' +i+ ');0))*24;'+night_timing[1]+';'+night_timing[0]+'))/24))/60').setNumberFormat('0.00').setHorizontalAlignment("center");
    total_night_hours = total_night_hours + sheet.getRange(i,totalColumns+1).getValue();
    // sheet.getRange(i,totalColumns+2).setValue(total_night_hours);
    // Code to count night hours

Holiday hours

The formula used is the following \( &=IF(NETWORKDAYS.INTL(Ai; Ai; "0000000";{'+ feast_days +'})=0;Ei;0) \) and I created a variable to store the increment. The code is more readable:

    // Code to count worked holiday hours
    sheet.getRange(i,totalColumns+2).setFormula('=IF(NETWORKDAYS.INTL(A' +i+ '; A' +i+ '; "0000000";{'+ feast_days +'})=0;E' +i+ ';0)').setHorizontalAlignment("center");
    total_feast_hours = total_feast_hours + sheet.getRange(i,totalColumns+2).getValue();
    // sheet.getRange(i,totalColumns+3).setValue(total_night_hours);
    // Code to count worked holiday hours

Worked days

The formula used is the following \( &=COUNTUNIQUE(ABfirst:ABlast) \) and I saved the value from a cell that I later cleared. Note that I reused the column that processes the colour line by line. The code:

// Code to count worked days
  sheet.getRange(totalRows+1,columnColorCalc).setFormula('=COUNTUNIQUE(AB3:AB' +totalRows+ ')').setNumberFormat('0');
  total_worked_days = sheet.getRange(totalRows+1,columnColorCalc).getValue();
  sheet.getRange(totalRows+1,columnColorCalc).clear(); // To clear the cell after storing the variable

Final sums

The code is easy and the image below show how the final calculations will appear:

sheet.getRange(totalRows+2,4).setValue('Σ=').setNumberFormat('0').setHorizontalAlignment("right");
sheet.getRange(totalRows+2,5).setFormula('=SUM(E2:E' +totalRows+ ')').setNumberFormat('0.00 \\h\\o\\u\\r\\s').setHorizontalAlignment("left"); // shows total duration
  
sheet.getRange(totalRows+3,4).setValue('Σnight('+night_timing[0]+'-'+night_timing[1]+')=').setNumberFormat('0').setHorizontalAlignment("right");
sheet.getRange(totalRows+3,5).setValue(total_night_hours).setNumberFormat('0.00 \\h\\o\\u\\r\\s').setHorizontalAlignment("right"); // shows total night hours

sheet.getRange(totalRows+4,4).setValue('Σholiday=').setNumberFormat('0').setHorizontalAlignment("right");
sheet.getRange(totalRows+4,5).setValue(total_feast_hours).setNumberFormat('0.00 \\h\\o\\u\\r\\s').setHorizontalAlignment("right"); // shows total holiday hours
  
sheet.getRange(totalRows+6,4).setValue('Σworked=').setNumberFormat('0').setHorizontalAlignment("right");
  if (total_worked_days == 1) {
    sheet.getRange(totalRows+6,5).setValue(total_worked_days).setNumberFormat('0 \\d\\a\\y').setHorizontalAlignment("right"); // shows total worked day
} else {
    sheet.getRange(totalRows+6,5).setValue(total_worked_days).setNumberFormat('0 \\d\\a\\y\\s').setHorizontalAlignment("right"); // shows total worked days
}
Final calculations
Final calculations

Download

You can find and test my Script on GitHub, Code_en.gs is the English version and Code_it.gs is the Italian one. Look at the following link:

TAGS MULTI

Add new comment

The content of this field is kept private and will not be shown publicly.
About text formats

Plain text

  • No HTML tags allowed.
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Add new comment

The content of this field is kept private and will not be shown publicly.
About text formats

Plain text

  • No HTML tags allowed.
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Sponsored Links
Pubblicità

Nicola Rainiero

A civil geotechnical engineer with the ambition to facilitate own work with free software for a knowledge and collective sharing. Also, I deal with green energy and in particular shallow geothermal energy. I have always been involved in web design and 3D modelling.