the difference in hours between times in google sheets

You can watch the video, but just make your times numeric, subtract and multiply by 24. You will need to use the format tab in sheets, below is the formula formatNumeric() is that step.

(formatNumeric(clock out) - formatNumeric(clock in)) * 24

 

 

Add timestamp to Google Sheets when data are entered

I wanted a time/date stamp for when a particular cell was populated with data.

First you will need to open the Script editor under Tools. Then paste and edit the below code.

 

function onEdit(event) { 
 var timezone = "GMT-4"; 
 var timestamp_format = "MM-dd-yyyy HH:mm"; // Timestamp Format. 
 var updateColName = "value"; // Column where data are populated
 var timeStampColName = "Timestamp"; // Column where timestamp is automatically populated
 var sheet = event.source.getSheetByName("data"); //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 'Timestamp' 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); } 
}

See here for original code as well as a video

Note that if you run the script (hit Play button) in the script editor you will get an error on line 6 simply because the “event” cannot be called in the editor. The code should still work fine.