# Thread: Tracking number of employees (office 2000)

1. ## 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

2. ## 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 ctrl-shift-enter) 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

3. ## 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 e-mail to me.
Thanks again for all the help,
Bill

4. ## 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 24-hr 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

5. ## 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")

&"/"&
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 28-84, 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•