» 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:
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
- Who Moved My Cheese? An Amazing Way to Deal with Change in Your Work and in Your Life
- Yes, You Can Time the Market!
- The Financial Numbers Game: Detecting Creative Accounting Practices
- How to Pay Zero Taxes (Annual)
- Harry Potter and the Order of the Phoenix (Book 5)
No comments have been submitted.
