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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
