Time table where workers overstay past midnight My input data comes in the following tabular table:



With this, I make a basic time table where I record arrival hour, leaving hour and total work hours.



Problem is, I can't do it with a simple pivot table because sometimes people will stay past midnight, so if I just get the MIN(Time) I'll sometimes get that the employee arrived at 1am. Another issue is I need to use the STATE to determine if it's a user arriving or leaving (denoted by "* Entrada" for arrivals and "* Salida" for leavers).

To work with this, I arbitrarily decided that the corresponding work day starts at 6am, so first I defined the following named ranges:


tblState: =OFFSET(data!$A$1,1,MATCH("STATE",data!$1:$1,0)-1,COUNTA(data!$A:$A)-1)
tblDate: =OFFSET(data!$A$2,0,0,COUNTA(data!$A:$A)-1)
tblName: =OFFSET(data!$A$1,1,MATCH("NAME",data!$1:$1,0)-1,COUNTA(data!$A:$A)-1)


And then my working formulas are

Arrived:

{=MAX((MIN(IF(((tblName=$B10)*(tblDate&gt;(D$8+0.25))*(RIGHT(tblState,7)="Entrada")*(WEEKDAY(D$8,2)&lt;6)*(tblDate&lt;(D$8+1)))&gt;0,tblDate))-D$8),0)}


Left:

{=MAX((MAX(IF(((tblName=$B10)*(tblDate&lt;(D$8+1.25))*(tblDate&gt;(D$8+0.25))*(RIGHT(tblState,6)="Salida"))&gt;0,tblDate))-D$8),0)}


Hours (I do this to take care of negatives and to substract the lunch hour from the working hours if the employee worked more than a half shift):

=MAX((IF(OR(D10=0,D11=0),0,IF((D11-D10)&lt;(4/24),D11-D10,MAX(D11-D10-(1/24),0)))),0)


It is incredibly inelegant and inefficient — it takes about 5 mins to calculate with ~25k records on my data table.

Another issue is that for the next month, I have to manually check for new records on the data table (add new employees to the time table) and missing records (remove employees who no longer work for the company), though I figure I could easily take care of this with a macro.

What would be a more efficient alternative?
It can be tough to come up with a definitive solution without having your data, but here's a few ideas that I would consider if I was having to solve this problem.

Since you have the full date and time in your data table, you could store that in your time table, and just display the time part of the date/time.  Then your elapsed time calculations would be simplified because you can use the difference between arriving and leaving that you're already calculating in the sheet.

Alternatively, if you could change your time table to allow multiple entries in one day for each person, you could have the person "leave" at midnight (23:59:59 or 24:00:00) the day the arrive, and "arrive" at 00:00:00 the next day.  This would still require some complexity in the time calculations.

If those ideas won't work for you, you can make a small improvement in your "hours" calculation.  The last part of it (MAX(D11-D10-(1/24),0)) is overly complicated.  You've just checked that (D11-D10)&lt;(4/24), you know that D11-D10 will be >= 4/24, so the MAX calculation could just be D11-D10-(1/24).