Monitoring the time spent at office by employees, check-ins and check-outs [Excel Model]

Suppose that you have implemented in your company a check-in/check-out system with identification card: the employees are entering the office by checking-in with their cards and are leaving the office by checking-out.

The management wants to monitor and review the time spent at office by each employee and they asked you to create a report with the daily “net hours” and “breaks” per date and person. You extracted the raw data from the system and you have it in Table 1 as shown bellow:

–> The lines with “Entries” mean that the person entered the building and the ones with “Exits” mean that they left the building.

How many hours spent each user from the table at the office ? If we sum the times with “Exits” type, subtract the sum of times with “Entries” and multiply the result with 24 we obtain the effective hours spent in the office. The problem appears in the situations when some users check-in/out with cards or their colleagues or the person from reception is opening the gate without they actually register with the card.

In order to be able to have an accurate report, ideally, the following checks should be fulfilled:

• For every Entry we must have an Exit. The total number of Entries per user and day must be equal with the total number of Exits.
• Cannot be 2 or more consecutive Entries or Exits. After each Entry we must have an Exit and after each Exit we can only have an Entry. (before we check this, we must sort the Table1 by Date, Name and Time)
• The first line of each day must be an Entry and the last line of each day must be an Exit.

Considering that sometimes it is difficult to have all these three elements in place, we can calculate some estimated results which we’ll check later in detail for each person and examine in other ways the situations where we cannot know exactly what happened in reality. For this, we must check the first and last entry of each day and the total hours between the time when the user left and arrived. Then we will calculate, for all the “breaks” during program, the time when user left and when he/she came back. Thus, we find the time of each break and if we subtract all break time from the total time calculated before we will obtain the final result: the “net hours” spent at office.

The final result in our simple example is the following:

To calculate these amounts I used some excel array formulas: confirmed with CTRL+SHIFT+ENTER:

• Min Entry: the first time of day when user checked-in. Min and IF array formula with multiple conditions:

• Max Entry: the last time of day when user checked-out: Max array formula with multiple conditions:

• The Exits during the day. Ex: first exit if it’s not the last: Small and IF array formulas with multiple conditions

• The Entries during the day. Ex: second entry after first exit: Small and IF array formulas with multiple conditions

You can download the model of this process and the above example with all the presented formulas plus more others here:

Checkin_Checkout-Monitoring_MODEL

For additional consulting, please use the Contact section of the Homepage.