Get the difference between dates in minutes

The following Google Apps Script is designed to get the difference (in minutes) between 2 dates from a Google Sheet. This was part of a tool used to create events from data in a Google Sheet where I needed to get the duration of the meeting for Zoom. The actual date, hour and minute values are separated in columns as it was easier to control user input in that format, so we will need to piece them together.

Start / End date values in a Google Sheet

First we need to get the values from the Google Sheet, here is how we do that for the Start time:

// get active sheet from  spreadsheet
var ss = SpreadsheetApp.getActiveSheet();

// get the start date
var startDate = ss.getRange(3,1).getValue();

// get the start hour
var startHour = ss.getRange(3,2).getValue();

// get the start minute
var startMin = ss.getRange(3,3).getValue();

 Next we need to add the hours and minutes to the date:

// add hours/minutes to start date
startDate.setHours(startHour);
startDate.setMinutes(startMin);

We repeat this for the end date also. Now we are ready to subtract the dates from each other and divide the result to get it in minutes:

// subtract dates and get difference between them in minutes
var duration = (endDate - startDate) / 1000 / 60;
Logger.log('Duration is: ' + duration + ' minutes');

 

Limitation

Be aware this code does not account for dates that overlap with changes in TimeZones (ie clocks going backwards/forwards).

File Download

Download the Get the difference between dates in minutes sheet here. Please use 'File' > 'Make a copy' for your own version.