still having trouble with this....

[Guest]

hi all, thanks for the help i got before but i am still having trouble, i
need create a function that returns a "head count" from my staff rota in
excel the rota format is below and each cell is in HH:MM format, what i need
excel to return is the number of people in the business between 07:00 and
08:00, 08:00 and 09:00 etc..
help me out, its driving me mad!!!

06:00 1 15:00
09:00 1 15:00
07:00 1 16:00
07:00 1 15:00
09:00 1 15:00
11:00 1 17:00

 

[Biff]

Hi!

What are the minimum and maximum times that people will be
present? For example: 6:00 AM to 8:00 PM

Are there any shifts that will span past midnight?

Biff

 

[Guest]

hi
shift cover 24 hours of a day (just to make it easy, lol)
although generally, most morning starters ar at 6am last finishers are 11pm,
however there are four that start 10pm and finish at 7am

does this mean you have an idea???

nick

 

[David McRitchie]

Hi Nick,
For more information read about Date and Time at
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm

B2: 22:00 C2: 06:00 D2: =C2-B2+(B2>C2)

The logical expression returns True or False. True
has a value of 1 so adds one day to the difference, which is
of course 24 hours. If the person works more than 24 hours
they will have to make an entry for each day.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Ragdyer]

If I understand what you're looking for, you should make a table containing
the times that you're looking to poll the total employee attendance number.

Say start times are in Column A, from A2 to A100
And end times are in Column B, from B2 to B100.

Start your polling table in Column H and I
In H2 enter, 8:00
In H3 enter, 9:00
In I2 enter, 8:59
In I3 enter, 9:59

Select all four cells and drag down to copy for 24 hours (rows).

Then, enter this formula in J2:

=SUMPRODUCT(($A$2:$A$101<=H2)*($B$2:$B$101>=I2))

And copy this down for the 24 hours.

This will give you the total number of employees present at each particular
hour of the day.