我使用在E列和F列中放置时间戳
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Timesheet" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { //checks the column
var nextCell = r.offset(-1, 5);
if( nextCell.getValue() === '' ) //is empty?
nextCell.setValue(new Date());
var nextCell = r.offset(0, 4);
if( nextCell.getValue() === '' ) //is empty?
nextCell.setValue(new Date());
}
}
我想得到H列中两个单元格之间的时间差(以小时为单位)(偏移量7)。我试过了:
var startTime = r.offset(-1, 4);
var stopTime = r.offset(-1, 5);
var totalTime = r.offset(-1, 7);
var difTime = stopTime - startTime;
totalTime.setValue(difTime);
但这只是给了我NaN。
尝试使用以下两行:
var difTime = stopTime - startTime;
roundedDayes.setValue(difTime);
我认为您将错误的变量传递给了setValue()
确保startTime
和stopTime
为日期格式
将其添加到代码中。
var startTime = r.offset(-1, 4);
var stopTime = r.offset(-1, 5);
var totalTime = r.offset(-1, 7);
var difTime = stopTime - startTime;
var difHours = Math.floor(difTime / (3600 * 1000));
totalTime.setValue(difHours );
这是一个有效的解决方案,我更改了测试的偏移值,只使用start=colA,stop=col B,并产生col C(或同一行上3个连续单元格的任何其他组合)
function test(){
var r = SpreadsheetApp.getActive().getActiveCell();
var startTime = new Date(r.offset(0, 0).getValue()).getTime();// get time in millisec
Logger.log(startTime);
var stopTime = new Date(r.offset(0, 1).getValue()).getTime();// get time in millisec
Logger.log(stopTime);
var totalTime = r.offset(0, 2); // define output range
var difTime = stopTime - startTime;
var difHours = Math.floor(difTime / (3600 * 1000)); // do the math
Logger.log(difTime);
totalTime.setValue(difHours ); // and update result cell
}