Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 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. #3
    2 Star Lounger
    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 e-mail to me.
    Thanks again for all the help,
    Bill

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 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. #5
    WS Lounge VIP sdckapr's Avatar
    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 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
  •