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.

Capture

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.

Capture

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.

Capture

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.

Capture

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.

Capture

Once you entered the formula in J5, drag it to cover all the id’s.




Now we have the time spent by each employee based on the calculation of first log in time and last lag out time.

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply