» Calculating Hours Worked
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- Microsoft Excel VBA Programming for the Absolute Beginner
- Special Edition Using Microsoft Excel 2002
- Harry Potter and the Order of the Phoenix (Book 5)
- Learn MS Excel 2002 VBA/XML Programming
- Keys to Reading an Annual Report (Barron's Business Keys)
No comments have been submitted.
