Results 1 to 6 of 6
  • Thread Tools
  1. 2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    154
    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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. 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

  4. 2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    154
    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>

  5. 2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    154
    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.

  6. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 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

  7. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 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
  •