Results 1 to 6 of 6
Thread: COUNT HELP (MSOFFICE2000)

20021215, 20:20 #1
 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.

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!
+ 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!

20021216, 05:34 #2
 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. 1012. 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 1012 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

20021216, 12:03 #3
 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>

20021216, 12:05 #4
 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.

20021216, 13:02 #5
 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

20021223, 06:59 #6
 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(