New answers tagged google-sheets-timestamp
0
SOLVED: =ArrayFormula(AVERAGE(FILTER(B2:B-A2:A,C2:C="A",B2:B>=DATEVALUE("2020-1-1"),B2:B<DATEVALUE("2020-4-1"))))
1
You can use the following formulas:
For 2020 use in cell D2
=IFERROR(AVERAGE(ArrayFormula(IF(YEAR($B$2:$B$37)=2020,($B$2:$B$37-$A$2:$A$37)))),0)
You will get a number like 4.475757576. Format the cell as Duration to get the hours (if not interested leave it as is).
Following that, you can use the following formula in cell D3
=INT(D2)&"D "& ...
0
The code is passing the timestamp as string / TEXT value but it's very likely that you aren't using the proper date format string but the simpler solution is to pass the Date object.
Replace
var time = Utilities.formatDate(new Date(), "GMT+1", "HH:mm:ss");
by
var time = new Date();
1
The following onEdit trigger will add the date to the adjoining column:
Code
ES6/v8/arrow/shorthand if statement
const onEdit = (e) => e.range.columnStart === 1 ? e.range.offset(0,1).setValue(new Date()) : null
classic
function onEdit(e) {
if(e.range.columnStart === 1) {
e.range.offset(0,1).setValue(new Date())
}
}
Explained
It reads as follows:
...
Top 50 recent answers are included
