0
1
I have a sheet used to calculate work-time for contractors and catch errors. Each row is a time-entry, with columns used to indicate the employee name, campus visited, date, time-in, and time-out.
I'm currently able to count the number of entries per employees per date per campus (i.e. John entered a time log for Campus Alpha 2 times for date 9/1/2018).
This is achieved through the following array formula stored at the top of the O column: =ArrayFormula(IF(ROW(C:C)=1,"Logs/Date",IF(ISBLANK(C:C), "", COUNTIF(D:D&CHAR(9)&N:N&CHAR(9)&C:C, D:D&CHAR(9)&N:N&CHAR(9)&C:C)
)))
Where column C is Campus, D is Date, and N is Employee Name.
I would like to extend this slightly, by only counting time-entries which fit the above criteria AS WELL AS have overlapping time. So if time-in and time-out do not overlap then it's not counted, if they do overlap in some way then I want to count the number of affected entries (as it's an error). Time-in is stored in column E and time-out is in column F.
I'm out of ideas on how to add that functionality to the pre-existing formula, any advice is greatly appreciated.
I've attached an example sheet at the following link: https://docs.google.com/spreadsheets/d/1WSZtDWuLrVf8oBbN4i6Y09tJCa2x5DM6EQ2IbcMewHE/edit?usp=sharing The entries highlighted in orange are examples that should be counted as their times overlap. The entries highlighted in green are not problem as their times do not overlap. The values in cell O5 and O6 should be 1 instead of 2.
It would help to have some sample data and the desired output – cybernetic.nomad – 2018-09-04T22:50:21.850
I've attached an example at the following page: (https://docs.google.com/spreadsheets/d/1WSZtDWuLrVf8oBbN4i6Y09tJCa2x5DM6EQ2IbcMewHE/edit?usp=sharing) The values in cell O5 and O6 should be 1 instead of 2
– Chandler – 2018-09-04T22:57:36.617@Chandler, it the Time Sheet found data overlaps each other for Time in & Out. But I'm not finding the Array formula anywhere,, better you share editable version then I'll add the formula there. – Rajesh S – 2018-09-05T07:40:38.907
@RajeshS Look at cell O1 (it's an
=ARRAYFORMULA()
I think it is special for Google) – Tom Brunberg – 2018-09-05T12:07:14.820@TomBrunberg, yes in Google Sheet instead of { } reserve word ARRAYFORMULA is to use, or when you finish CSE it prfixes ARRAYFORMULA. – Rajesh S – 2018-09-05T12:19:07.570
@RajeshS yes it is an ARRAYFORMULA stored in cell O1. I've provided a copy which is editable: https://docs.google.com/spreadsheets/d/1b7g3VVGaEXlnvSv79JUnB8cB2zM05IqDDlJ5OxNr3Wo/edit?usp=sharing
– Chandler – 2018-09-11T19:06:32.647