CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
pawn 57
pawn 57

Reputation: 1

i need to calculate a time difference in google sheets

I have a google sheet, called “test time stuff” It uses a script to automatically add the date and time (in colum “B”) , any time you add an entry Into the event column.

This is the script......

function onEdit(event)
{ 
  var timezone = "GMT-5";
  var timestamp_format = "E,M/dd H:mm a"; // Timestamp Format. 
  var updateColName = "event";
  var timeStampColName = "date / time";
  var sheet = event.source.getSheetByName('Sheet1'); //Name of the sheet 
where you want to run this script.


  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = 
updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only 
timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
     var date = Utilities.formatDate(new Date(), timezone, 
 timestamp_format);
    cell.setValue(date);
   }
}

When I do this i would like the “time in hours and minutes between events” to Automatically show the time in hours and minutes in that column. How can I accomplish this? Thanks.

Here is a link to the sheet...

https://docs.google.com/spreadsheets/d/1eyrAzpOFKxDHxs05DYovx3NMrl6XaofXsR24Yt9UzlM/edit?usp=sharing

Here is a link to sheet that can be edited...

https://docs.google.com/spreadsheets/d/1RqpVOsy2bzgqnhFHV7m1sae10o9pHA5cdEJ5Ijvl2C8/edit?usp=sharing

Upvotes: 0

Views: 1026

Answers (1)

Vytautas
Vytautas

Reputation: 2286

Really depends on how flexible you want it to be. If you do not care that a user will enter a value leaving an empty row and then fill in the blank row later, then it's easy. All you need to do is

var prevEvent = sheet.getRange(sheet.getLastRow(), 2).getValue() //get the previous event time

Now you do need to be sure you are using an actual JavaScript date format and simply calculate the difference. You can read how to do that in one of the examples here. It's basically the same thing, only your start and end are defined by the new value you are setting an the previous value. As I mentioned, this will not work properly if the last value is let's say in row 5 and someone enters a value in row 7 and then enters something in row 6 afterwards. The next entry in row 8 will only consider row 7 and not 6.

To work around that, you would need to get all the values with .getValues() and then loop through them, selecting which time was the last, but it can be done. Just takes a bit more fidling around with a for loop

Upvotes: 1

Related Questions