How many are at work every hour in a 24-hour day?

[LEG]

Help!
I am working on a spreadsheet where I need to calculate â€How many are at
work every hour in a 24 hour day and then someâ€.
My timesheet is exported from a personnel database which only gives me a
start time and an end time. I have converted start time and end time from the
hh:mm format to a number – also taking in account if the hour exceeds
midnight.
f.eks.: 1. September:
04:00 – 11:00 converted to 4 – 11
06:30 – 14:00 converted to 6.5 – 14
07:00 – 23:00 converted to 7 – 23
09:00 – 17:00 converted to 9 – 17
15:00 – 23:00 converted to 15 - 23
22:00 – 06:00 converted to 22 – 30
23:00 – 07:00 converted to 23 – 31
24:00 – 08:00 converted to 24 – 32
I now have to find out how many are at work in a 24-hour period broken down
into 1 hour intervals (7-8, 8-9, 16-17, 17-18, 18-19, 30-31 etc) Eksp. In
the above there are 3 in the time from 7-8, and 1 from 7-8 the next morning
Kan excel do this? I have 30 worksheets (1 for each day of a month) and
each day has a list of 200 names! And I have tried if, sumif, countif, and,
or and a combination of all the above! I have excel 2007.

 

[David Biddulph]

If your start hours are in column C and your finish hours in column D, the
figure for the 1-hour period from 7 to 8 would be given by
=SUMPRODUCT((INT(C2:C9)<=F$1)*(D29>=F$1+1)) if 7 is in F$1
The figure for the 1-hour period from 7 to 8 the following day would be
=SUMPRODUCT((INT(C2:C9)<=F$1+24)*(D29>=F$1+25))

 

[LEG]

Hi - sorry for the late respons, but thanks it worked! And saved me a lot of
hours trying to figure it out for myself!