Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
have data in one cell that shows workers hours by day 04:00-12:30 based on a
7 day work week with days off which would show as "off" or "/" have several employees that work different hours but require so many works on the job through out the day to complete a task which in turn need to make sure I have sufficient workers on duty looking for a formula on how many employees working on half hour |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
Maybe like this: Code: -------------------- ---A--- --B-- --C-- D --E-- --F-- 1 In Out Time Staff 2 Abel 14:00 22:30 04:00 2 3 Bob 08:00 16:30 04:30 2 4 Charlie 07:00 15:30 05:00 3 5 Dan 07:00 15:30 05:30 3 6 Evie 09:00 17:30 06:00 3 7 Frank 05:00 13:30 06:30 3 8 Ida 08:00 16:30 07:00 7 9 Juliet 09:00 17:30 07:30 7 10 Ken 12:00 20:30 08:00 9 11 Lenny 04:00 12:30 08:30 9 12 Mary 15:00 23:30 09:00 11 13 Nancy 10:00 18:30 09:30 11 14 Oscar 13:00 21:30 10:00 15 15 Penny 04:00 12:30 10:30 15 16 Quentin 10:00 18:30 11:00 16 17 Randy 14:00 22:30 11:30 16 18 Sam 11:00 19:30 12:00 17 19 Tom 07:00 15:30 12:30 15 20 Urie 07:00 15:30 13:00 16 21 Vic 14:00 22:30 13:30 15 22 Yanny 10:00 18:30 14:00 18 23 Zeb 10:00 18:30 14:30 18 24 15:00 19 25 15:30 15 26 16:00 15 27 16:30 13 28 17:00 13 29 17:30 11 30 18:00 11 31 18:30 7 32 19:00 7 33 19:30 6 34 20:00 6 35 20:30 5 36 21:00 5 37 21:30 4 38 22:00 4 39 22:30 1 40 23:00 1 41 23:30 0 -------------------- The formula in F2 and down is =SUMPRODUCT( ($B$2:$B$19 <= E2) * ($C$2:$C$19 E2) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27209 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
Can we have a little more information please? Where are you workers names...
in a row or column (which row or column would be helpful too)? Which row or column is the worker's hours in? When does your work day start and end? For days off... when you use a "/"... is that all that is in the cell or is there other text combined with it? Anything else about your layout that would affect the calculation (lunch or break times for example)? You have to remember that when you ask a question on a newsgroup, no one here know anything about the layout you are using, so you have to tell us, in enough detail, so we can visualize what your set up is. -- Rick (MVP - Excel) "David" wrote in message ... have data in one cell that shows workers hours by day 04:00-12:30 based on a 7 day work week with days off which would show as "off" or "/" have several employees that work different hours but require so many works on the job through out the day to complete a task which in turn need to make sure I have sufficient workers on duty looking for a formula on how many employees working on half hour |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
Workers Name are in column "a" then Sunday thru Saturday
columns b thru h the cells below the day of the week would show the shift for each employee such as 04:00-12:30 I wanted to populate the hours of the day below each day of the week at 30 min time frame hope this helps "Rick Rothstein" wrote: Can we have a little more information please? Where are you columeworkers names... in a row or column (which row or column would be helpful too)? Which row or column is the worker's hours in? When does your work day start and end? For days off... when you use a "/"... is that all that is in the cell or is there other text combined with it? Anything else about your layout that would affect the calculation (lunch or break times for example)? You have to remember that when you ask a question on a newsgroup, no one here know anything about the layout you are using, so you have to tell us, in enough detail, so we can visualize what your set up is. -- Rick (MVP - Excel) "David" wrote in message ... have data in one cell that shows workers hours by day 04:00-12:30 based on a 7 day work week with days off which would show as "off" or "/" have several employees that work different hours but require so many works on the job through out the day to complete a task which in turn need to make sure I have sufficient workers on duty looking for a formula on how many employees working on half hour |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
You didn't answer all the questions I asked...
1) What time does the day's work start at and what time does the days work end at (or do you want a 24-hour report by half-hours per day)? 2) When you use a "/" for days off, is that all that is in the cell or is there other text coupled with it? 3) Are there any breaks in the schedule when no work is performed, such as for lunch? -- Rick (MVP - Excel) "David" wrote in message ... Workers Name are in column "a" then Sunday thru Saturday columns b thru h the cells below the day of the week would show the shift for each employee such as 04:00-12:30 I wanted to populate the hours of the day below each day of the week at 30 min time frame hope this helps "Rick Rothstein" wrote: Can we have a little more information please? Where are you columeworkers names... in a row or column (which row or column would be helpful too)? Which row or column is the worker's hours in? When does your work day start and end? For days off... when you use a "/"... is that all that is in the cell or is there other text combined with it? Anything else about your layout that would affect the calculation (lunch or break times for example)? You have to remember that when you ask a question on a newsgroup, no one here know anything about the layout you are using, so you have to tell us, in enough detail, so we can visualize what your set up is. -- Rick (MVP - Excel) "David" wrote in message ... have data in one cell that shows workers hours by day 04:00-12:30 based on a 7 day work week with days off which would show as "off" or "/" have several employees that work different hours but require so many works on the job through out the day to complete a task which in turn need to make sure I have sufficient workers on duty looking for a formula on how many employees working on half hour |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
Sorry Rick
24 hour report Half hours / equals to a day the employee is not at work such as leave or training day, Off equals employees regular day off. Am not concerned with breaks or lunchs that is left to supervison based on current work loads. David "Rick Rothstein" wrote: You didn't answer all the questions I asked... 1) What time does the day's work start at and what time does the days work end at (or do you want a 24-hour report by half-hours per day)? 2) When you use a "/" for days off, is that all that is in the cell or is there other text coupled with it? 3) Are there any breaks in the schedule when no work is performed, such as for lunch? -- Rick (MVP - Excel) "David" wrote in message ... Workers Name are in column "a" then Sunday thru Saturday columns b thru h the cells below the day of the week would show the shift for each employee such as 04:00-12:30 I wanted to populate the hours of the day below each day of the week at 30 min time frame hope this helps "Rick Rothstein" wrote: Can we have a little more information please? Where are you columeworkers names... in a row or column (which row or column would be helpful too)? Which row or column is the worker's hours in? When does your work day start and end? For days off... when you use a "/"... is that all that is in the cell or is there other text combined with it? Anything else about your layout that would affect the calculation (lunch or break times for example)? You have to remember that when you ask a question on a newsgroup, no one here know anything about the layout you are using, so you have to tell us, in enough detail, so we can visualize what your set up is. -- Rick (MVP - Excel) "David" wrote in message ... have data in one cell that shows workers hours by day 04:00-12:30 based on a 7 day work week with days off which would show as "off" or "/" have several employees that work different hours but require so many works on the job through out the day to complete a task which in turn need to make sure I have sufficient workers on duty looking for a formula on how many employees working on half hour |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need formula for counting employees by hour
Give this macro a try...
Sub Create15MinuteIntervals() Dim X As Long Dim Y As Long Dim Z As Long Dim LastRow As Long Dim QuarterHours(0 To 95, 2 To 8) As Long Dim Parts() As String With Worksheets("Sheet1") LastRow = .Cells(2, "A").End(xlDown).Row For X = 2 To 8 For Y = 2 To LastRow Parts = Split(.Cells(Y, X).Value, "-") If UBound(Parts) = 1 Then For Z = DateDiff("n", 0, CDate(Parts(0))) \ 15 To _ DateDiff("n", 0, CDate(Parts(1))) \ 15 QuarterHours(Z, X) = QuarterHours(Z, X) + 1 Next End If Next Next LastRow = LastRow + 2 .Rows((LastRow) & ":" & (LastRow + 96)).Clear For X = 1 To 8 For Y = 0 To 95 If X = 1 Then .Cells(LastRow + Y, 1).Value = Format(TimeSerial(0, 15 * Y, 0), _ "hh:mm") & " - " & Format(TimeSerial(0, 15 * Y + 14, 0), "hh:mm") ElseIf QuarterHours(Y, X) < 0 Then .Cells(LastRow + Y, X).Value = QuarterHours(Y, X) End If Next Next End With End Sub -- Rick (MVP - Excel) "David" wrote in message ... Sorry Rick 24 hour report Half hours / equals to a day the employee is not at work such as leave or training day, Off equals employees regular day off. Am not concerned with breaks or lunchs that is left to supervison based on current work loads. David "Rick Rothstein" wrote: You didn't answer all the questions I asked... 1) What time does the day's work start at and what time does the days work end at (or do you want a 24-hour report by half-hours per day)? 2) When you use a "/" for days off, is that all that is in the cell or is there other text coupled with it? 3) Are there any breaks in the schedule when no work is performed, such as for lunch? -- Rick (MVP - Excel) "David" wrote in message ... Workers Name are in column "a" then Sunday thru Saturday columns b thru h the cells below the day of the week would show the shift for each employee such as 04:00-12:30 I wanted to populate the hours of the day below each day of the week at 30 min time frame hope this helps "Rick Rothstein" wrote: Can we have a little more information please? Where are you columeworkers names... in a row or column (which row or column would be helpful too)? Which row or column is the worker's hours in? When does your work day start and end? For days off... when you use a "/"... is that all that is in the cell or is there other text combined with it? Anything else about your layout that would affect the calculation (lunch or break times for example)? You have to remember that when you ask a question on a newsgroup, no one here know anything about the layout you are using, so you have to tell us, in enough detail, so we can visualize what your set up is. -- Rick (MVP - Excel) "David" wrote in message ... have data in one cell that shows workers hours by day 04:00-12:30 based on a 7 day work week with days off which would show as "off" or "/" have several employees that work different hours but require so many works on the job through out the day to complete a task which in turn need to make sure I have sufficient workers on duty looking for a formula on how many employees working on half hour |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurrence of day of week AND hour of day | Excel Discussion (Misc queries) | |||
Counting The Number of Employees | Excel Discussion (Misc queries) | |||
Counting the number of cells within a certain hour. | Excel Discussion (Misc queries) | |||
formula used for FTE employees | Excel Discussion (Misc queries) | |||
Formula designed to reimburse employees up to a certain amt? | Excel Worksheet Functions |