COUNT HELP (MSOFFICE2000)

[cvbs]

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.

[AndrewO]

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

[cvbs]

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>

[cvbs]

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.

[HansV]

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

[HansV]

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(