Results 1 to 5 of 5

20030803, 23:05 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
Tracking number of employees (office 2000)
Good Evening to all,
I addressed this issue once before on this forum , however I could not find the post to bring it back again. Therefore I will restate my problem. I am trying to track the number of employees that are listed to be working in the A.M shift. From opening to 12:00P.M. Then those that are working the Afternoon shift from 12:00 to 6:00P.M. Then once again tracking the number scheduled for the close shift .... from 6:00P.M. to latest time recorded.
If a person does a shift from opening to closing time, they should be counted in all categories. If they open the store and work till 6:00 P.M. they would be counted in the A.M. group and also the Afternoon Shift. I hope this makes the request clear. I want to structure it as in example worksheet attached in cell b87 and c87 so I need to format the result to show just like that (ie) 8/10/3
This formula needs to be able to handle up to 50 people.
I do apologize for not being able to find the original post and I want to say thanks in advance for all the help.
Bill

20030804, 11:02 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Tracking number of employees (office 2000)
If you want a formula, the best thing to do is to have a separate column for In/out/total instead of tacking it onto the name or to not have a blank cell in A since it screws up the array formulas when you try to work with them.
I inserted a column ([img]/forums/images/smilies/cool.gif[/img] so all your columns were increased by (B to C, C to D, etc). In this column I have the pattern in, out, <blank>, hours, <blank> as you have at the end of A.
Then in C87 (what WAS your B87) I have ARRAY (confirm with ctrlshiftenter) in ONE line:
=TEXT(SUM(IF(ISTEXT($B$27:$B$83)*($B$27:$B$83="in" )*(C$27:C$83<>"OFF")*(C$27:C$83<0.5),1,0)),"0")&"/"&TEXT(SUM(IF(ISTEXT($B$27:$B$83)*(C$27:C$83<>"OFF ")*(($B$27:$B$83="in")*(C$27:C$83<=0.75)*($B$28:$B $84="out")*(C$28:C$84>=0.5)),1,0)),"0")&"/"&TEXT(SUM(IF((C$28:C$84<>"OFF")*ISTEXT($B$28:$B$8 4)*($B$28:$B$84="out")*(C$28:C$84>0.75),1,0)),"0")
This is copied to D87:I87 (your C87:H87).
Steve

20030805, 11:00 #3
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Tracking number of employees (office 2000)
sdckapr
Good Morning and a very LARGE THANK YOU for the answer to this question. However, I would like to know how the formula works. If you would be so kind to explain where the .5 and .75 come in to play in the calc I would learn a great deal. Not that I don't appreciate the help, but, I would like to learn so that I can use the knowledge in the future. If you don't want to post it here because of length or whatever feel free to send a direct email to me.
Thanks again for all the help,
Bill

20030805, 11:10 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Tracking number of employees (office 2000)
0.5 = NOON = 12/24
0.75 = 6PM = 18/24
Excel stores time/dates in NUMBER of days. The serialdate is the number of days since Dec 31, 1899 (serial date = 0).
The INTEGER portion of the serial date is the "DATE" the decimal portion is the "time". The time is also in "fractions of a day"
If you multiply the decimal portion of the serialdate by 24 you get the number of hours since MIDNIGHT (0:00). Conversely if you take the time (6PM for example), convert to 24hr time = 18:00 means 18 hours so in serialtime it is 18/24 = 0.75 (3/4ths of the day has progressed is also a valid way to look at it). at NOON, it is the middle of the day!
Steve

20030805, 11:40 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Tracking number of employees (office 2000)
Explanation of the mega formula:
Note: this is an ARRAY function as I mentioned. For a general discussion of these see Chip Pearson's overview.
1st break it up into portions:
"count of morning workers":
TEXT(SUM(IF(ISTEXT($B$27:$B$83)*($B$27:$B$83="in") *(C$27:C$83<>"OFF")*(C$27:C$83<0.5),1,0)),"0")
Add the "divider"
&"/"&
Count of Afternoon workers"
TEXT(SUM(IF(ISTEXT($B$27:$B$83)*(C$27:C$83<>"OFF") *(($B$27:$B$83="in")*(C$27:C$83<=0.75)*($B$28:$B$8 4="out")*(C$28:C$84>=0.5)),1,0)),"0")
Another "divider"
&"/"&
Count of evening employees
TEXT(SUM(IF((C$28:C$84<>"OFF")*ISTEXT($B$28:$B$84) *($B$28:$B$84="out")*(C$28:C$84>0.75),1,0)),"0")
The afternoon is most complicated so let's do that first:
The section:
TEXT(................... , "0")
Just FORMATS it to be an integer. It needs to be string since we are creating a string.
SUM(....) just sums the "Array/Range"
This is the IF statement. If the "condition is true" than have 1, otherwise 0. A "1" indicates the INDIVIDUAL worker is in the shift (afternoon in this example) a zero means he/she is not. The sum of these is the total number of workers in the shift.
IF(......, 1,0)
We have several conditions. A TRUE for each one MULTIPLIES like a 1, a FALSE like a zero, therefore multiplying mult conditions will be TRUE overall (=1) ONLY if there are NO False statements. Any false will make the product = 0 (FALSE). so multiplication is like an "AND". [Conversely a "+" is like an OR]
The conditions to be met are (ALL Must be true!):
ISTEXT($B$27:$B$83) The value in column B for a row must be text (this is needed since some are blank!)
(C$27:C$83<>"OFF") Do NOT want any with workers marked OFF
($B$27:$B$83="in") Worker must be IN
(C$27:C$83<=0.75) Before 6PM (if he is in after 6 PM he is NOT in afternoon shift)
($B$28:$B$84="out") Worker must be OUT
(C$28:C$84>=0.5)) After NOON (if he is Out before noon he is NOT in afternoon shift)
The rows for the in are from 27 83 but OUT are for 2884, since in/out are different rows. I want IN/TIme of row 1 to correspond with OUT/Time of the NEXT row.
So the eqn essentially states if the worker is IN before 6 and he is OUT after noon then he should be counted (=1) and then it SUMS all the individuals and converts it to text.
The Morning shift is similar, the conditions are simpler:
TEXT(SUM(IF(ISTEXT($B$27:$B$83)*($B$27:$B$83="in") *(C$27:C$83<>"OFF")*(C$27:C$83<0.5),1,0)),"0")
ISTEXT($B$27:$B$83) The value in column B for a row must be text (this is needed since some are blank!)
(C$27:C$83<>"OFF") Do NOT want any with workers marked OFF
($B$27:$B$83="in") Worker must be IN
(C$27:C$83<0.5) Before Noon (if he is in after NOON he is NOT in morning shift)
And for the evening:
TEXT(SUM(IF((C$28:C$84<>"OFF")*ISTEXT($B$28:$B$84) *($B$28:$B$84="out")*(C$28:C$84>0.75),1,0)),"0")
ISTEXT($B$28:$B$84) The value in column B for a row must be text (this is needed since some are blank!)
(C$28:C$84<>"OFF") Do NOT want any with workers marked OFF
($B$28:$B$84="out") Worker must be OUT
(C$28:C$84>=0.75)) After 6PM (if he is Out before 6PM he is NOT in evening shift)
Hope this helps,
Steve