First Login, Last Log Out Time In Excel

Through this post we will see how we could calculate the first log in time and last log out time in excel to calculate the number of hours spent in the office.

Generally we have the data in excel with id’s and their log in and log out times in other columns.

Calculating number of hours spent in the office with the help of first log in time and last log out is a common practice and we will help to understand how we could calculate that.

We have the log in and log out data as shown below.

And we want the result to look like this, the id along with their first log in and last log out and the time interval between these two.

Step 1

The logic behind calculating the difference between first log in time and last log out time is to find the minimum log in time and maximum log out time for each id.

Enter formula =MIN(IF($A:$A=G5,$B:$B)) in cell H5 and it’s an array formula so you need to press Ctrl+Shift+Enter after entering it into cell.

Press Ctrl+Shift+Enter after inserting formula, this calculates the minimum log in time for employee a645484 and drag the formula till H7 to calculate for all three employee.

Step 2

Now we will calculate the maximum log out time for each employee, to do so we will have to calculate the maximum of log out time for each employee.

Enter formula =MAX(IF($A:$A=G5,$C:$C)) in cell I5 and it’s an array formula so you need to press Ctrl+Shift+Enter after entering it into cell.

Press Ctrl+Shift+Enter after inserting formula, this calculates the maximum log out time for employee a645484 and drag the formula till I7 to calculate for all three employee.

Step 3

Now we have the minimum log in time and maximum log out time for each employee, we could calculate the time spent by them in the office by subtracting the log in time from log out time.

To do so enter the formula =TEXT(I5-H5, “h:mm:ss”) in the cell J5, the values are all time value so we just have to format it with the help of TEXT function as shown in figure.