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

20021215, 21:20 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 166
 Thanks
 1
 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.

20021216, 06: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, 13:03 #3
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 166
 Thanks
 1
 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, 13:05 #4
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 166
 Thanks
 1
 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, 14:02 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 25 Times in 25 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, 07:59 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 25 Times in 25 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(