Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNT HELP (MSOFFICE2000)

    Hello to all in the know,
    I am currently developing a sheet to do schedules. ( file attached ) The one piece of the puzzle that I am having problem with is a formula that will show me the number of employees working during the day at any one given time slot. (ie) From 10:00 am to 12:00pm-- I have 2 people on the schedule -- From 12:00 to 6:00 I show 3 people and then from 6:00 to 9:00 I show 2. I want this breakdown to show in a cell just below the day in a format like 2/4/3. (as in cell C32, which I put in manually just for information ) If any one has any ideas please let me know.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT HELP (MSOFFICE2000)

    I assume that there are several issues to thread through:
    1 there are a lot of employees that would prevent a single complex IF working
    2 start times can vary by individual, stop times can vary (i.e. there are not three 'standard' shifts for people) - if there were it may be a lot simpler

    The spreadsheet does not lend itself to easy calculations but a method springs to mind.
    I'm assuming that the three intervals you're interested in a 'fixed' - e.g. 10-12. If they weren't - the following could use time variables but be limited to three such variable sets at any one time

    You already have a hidden row under each associate. I'd suggest three more - one for each interval of interest
    The formula can simply 'count' a one if the particular person is ON at the interval for that row e.g. for the 10-12 check =IF(ontime=<TIME(10,0,0),IF(offtime>TIME(12,0,0),1 ,0),0)
    Your summary can then just SUM or COUNT the 1s for the set of hidden rows.

    A modification of the above is to use a unique indicator rather than the 1 - e.g. "MorningTea" and do a final COUNTIF for "MorningTea" in the column

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT HELP (MSOFFICE2000)

    Hans,
    Thank you for the quick response. I have looked at the attachment and it does the job very well. If you were not told that you are a genius then let me be the first, if so, let me join the throngs in their cheers. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT HELP (MSOFFICE2000)

    Andrew,
    Thank you for the answer. I find it great that there are so many helpful people out here. This is truly the best site for help in this program.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: COUNT HELP (MSOFFICE2000)

    If you have less than 10 employees, you can use something like the attached workbook. I have left the auziliary rows visible; you would want to hide them in the final version.

    The formula in C6

    =100*AND(C3<=TIME(10,0,0),C4>=TIME(12,0,0))+10*AND (C3<=TIME(12,0,0),C4>=TIME(18,0,0))+AND(C3<=TIME(1 8,0,0),C4>=TIME(21,0,0))

    is based upon the fact that TRUE = 1 and FALSE = 0. It returns a number like 110 meaning that the employee is present from 10 AM to 12 PM, present from 12 PM to 6 PM, and not present from 6 PM to 9 PM. I have assumed that you only want to count presence if the employee is available during the entire period of time.

    To get a total, the numbers are just summed over all employess, and than formatted to show slashes / between the digits.

    If you have more than 10 but less than 100 employees, the formulas can be adapted: use 10000 and 100 as multiplication factors instead of 100 and 10, and set the number format to 00"/"00"/"00
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: COUNT HELP (MSOFFICE2000)

    I'll try to explain how the formula works.

    Note: your browser may display & l t (without the spaces) instead of the "less than" symbol, and & g t (without the spaces) instead of the "greater than" symbol.

    As an example, take an emplyee with In = 9:00 AM and Out = 5:00 PM for a certain date. The formula with In and Out substituted for the actual cell references is:

    =100*AND(In<=TIME(10,0,0),Out>=TIME(12,0,0))+10*AN D(In<=TIME(12,0,0),Out>=TIME(18,0,0))+AND(In<=TIME (18,0,0),Out>=TIME(21,0,0))

    The formula sums three parts:

    100*AND(In<=TIME(10,0,0),Out>=TIME(12,0,0))

    10*AND(In<=TIME(12,0,0),Out>=TIME(18,0,0))

    AND(In<=TIME(18,0,0),Out>=TIME(21,0,0))

    The TIME function takes 3 arguments: hours (based on a 24 hour clock), minutes and seconds. It returns this as a time value Excel can use. For example TIME(18,0,0) returns the time value for 18:00:00 in a 24 hour clock, i.e. 6 PM.

    In<=TIME(10,0,0) tests whether the In time is on or before 10 AM; it returns TRUE or FALSE.
    Out>=TIME(12,0,0) tests whether the Out time is on or after 12 PM; it returns TRUE or FALSE.
    AND(

Posting Permissions

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