Count Days Worked

[Steph]

Hi everyone. I have a data sheet that has a bunch of employees and the time
they worked. I am trying to figure out how to count the number of days each
employee worked in that week. The problem is that a given employee may have
worked on different tasks, and therefore shows up on more than i line (like
Emp1). The way I was doing it is on each row, I was doing a simple count
function. Then, on a different summary sheet, I used sumif to consolidate
the total hours worked. That works great for total hours worked, but # of
days worked return more than the actual # of days. So for Emp1, it returned
14. How can I formulate that so it returns 6? Thank you!!!!

Day1 Day2 Day3 Day4 Day5 Day6 Day7 Total
Emp 1 5.00 3.50 3.00 2.00 2.00 15.50
Emp 1 3.00 2.00 2.25 7.25
Emp 1 3.00 3.00 1.00 7.00
Emp 1 3.00 3.50 2.00 8.50
Emp 2 8 8.00 8.00 8.00 8.00 40.00
Emp 3 7.00 7.00 7.00 21.00

 

[Bill Kuunders]

Sounds like you have enough excel under your belt to do this with a few "if"
functions...........

My approach would be to add all the hours for each employee per day.
This could be done next to your existing list.
Add another 8 columns etc

When the employee name in column A is the same as the one above this line
add this day to the line above.

as an example j column will get all the day1 hours
function to use as a base......enter in j7..............=if(a6=a7,j6+b7,b7)

In this way you will get a cumulative list per employee
Then you would check in each subtotal line how many cells with 0 value and
subtract that from 7 , you'll need another if function here to determine
whether
you do have a subtotal line or not.

Have fun

Regards
Bill K

 

[David]

Hi Steph,
Couldn't figure out a way to do it with formulas. The set up:
Emp Day1 Day2 Day3 Day4 Day5 Total DayCt
Emp1 5.00 3.50 3.00 11.50 3.00
Emp1 3.00 2.00 2.25 2.00 2.00 11.25 5.00
Emp1 3.00 3.00 1.00 7.00 3.00
Emp1 3.00 3.50 2.00 8.50 3.00
Emp2 8.00 8.00 8.00 8.00 8.00 40.00 5.00
Emp3 8.00 7.00 7.00 22.00 3.00
100.25


Emp1 38.25 5
Emp2 40 5
Emp3 22 3
100.25
Days 6 & 7 are hiddenrows, so I could I could paste it here. The summarry is
below the data, again for ease of presentation. There is a named range,
"EmpSummary". It is assumed that the this summary is a list of employees,
exactly as they appear in the data section.
Code:
Sub EmpCtDays()
Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
EmpName = ActiveCell.Value
DayCt = ActiveCell.Offset(0, 9).Value
ReturnAddress = ActiveCell.Address
DayCt2 = ActiveCell.Offset(1, 9).Value
If ActiveCell.Offset(1, 0).Value <> EmpName Then
If DayCt > DaySave Then DaySave = DayCt
GoSub WriteDays
Else
If DayCt2 > DayCt Then
DaySave = DayCt2
Else
If DayCt > DaySave Then DaySave = DayCt
End If
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
If ActiveCell.Value = "" Then GoTo Done
WriteDays:
Application.Goto Reference:="EmpSummary"
Selection.Find(What:=(EmpName), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 9).Value = DaySave
Range(ReturnAddress).Select
DaySave = 0
Return
Done:
End Sub

(If this is too hard to deal with, post here and I will get a notification,
leave your email and I will send the file)
Thanks. Hope it works, don't have all your data, so notsure that all
contingencies have been tested