The following little piece of code gives you the result, without prior knowledge about the character code number.
Code
function mySplit(range, delimiter) {
var output = [], maxCol = range[0].length;
for(var i=0, iLen=range.length; i<iLen; i++) {
var colCounter=0, counter=0;
for(var j=0, jLen=range[0].length; j<jLen; j++) {
if(range[i][j] == "") {
colCounter++;
} else {
if(range[i][j].split(delimiter).length > 1) {
counter++;
}
}
}
output.push(counter + "/" + (maxCol - colCounter));
}
return output;
}
Screenshot
![enter image description here](https://i.stack.imgur.com/QOEIp.png)
![enter image description here](https://i.stack.imgur.com/Zuwm1.png)
Example
I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.
I made a simple version of your problem you can play around with:
Make a copy of the sheet
There are basically two things to do:
1. Get the duration for each visit
For that I added a Column with an arrayforumla (so we don’t have to copy it in every row) that basically looks if we are in a “Leave” row and if so, gives us the time difference between the leave and the arrival before that.
=ArrayFormula(
IF(
A2:A="Left";
TO_PURE_NUMBER(B2:B) - TO_PURE_NUMBER(B1:B);
)
)
(expanded for better readability
(Note: Your table needs to be well formated for that, meaning Leave and Arrival should always alternate)
Now you already got the duration for each visit.
Aggregate for each day
To make this simpler, lets add a Col to our sheet, that figures out the day without time for each row. Like this:
=ArrayFormula(IF(ISBLANK(B2:B);;INT(B2:B)))
The important part is the INT(B2:B)
which converts your datetime
to a date
Now I would recommend to create another sheet and automatically generate a day overview there, by simply putting a query
in A1.
=query(
Log!A1:D;
"select C, sum(D) where A = 'Left' group by C label C 'day', sum(D) 'total time'"
)
That’s it! Here is the sheet.
Best Answer
It's better to have percentage on the left, next to the name. Otherwise, in a few months you will be scrolling way over to the right to see someone's percentage.
Use the
average
function, which takes the average of the values. And format the cell with this formula as a percentage, so that 0.6 is shown as 60%. (See Format > Number > Percent in the menu).Example:
=average(C2:2)
is the average of all numbers in row 2, from column C to the last column (whatever it is). Put this in column B, copy it down, format as percentage and you won't need to change these formulas again.