Problem:

Columns A:C contain ID’s, dates and times. Each row indicates the time a particular person signed in or out of work.

We want to determine which of the entries in column C are times signed in and which are times signed out, and then we want to calculate the number of hours worked by each person.

Solution:

To determine whether a time represents signing in or out, enter the CHOOSE, MAX, and ROW functions as shown in the following Array formula in column D:

{=CHOOSE(MAX((A2=$A$2:$A$9)*(ROW()<>ROW($A$2:$A$9))*((B2&C2)<($B$2:$B$9&$C$2:$C$9)))+1,”Time Out”,”Time In”)}

Thus, “Time In” or “Time Out” will be displayed next to each time shown in column C.

Then, to calculate the number of hours worked by each person, use the SUM and IF functions as shown in the following Array formula in column E:

{=SUM(IF(D2=”Time Out”,(A2=$A$2:$A$9)*($D$2:$D$9=”Time In”)*(C2-$C$2:$C$9),0))}

Thus, the number of worked hours will be displayed next to the “Time Out” indicator matching each ID.

To apply Array formula:

Select the cell, press and simultaneously press .

Screenshot // Calculating Hours Worked

Calculating Hours Worked