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.

--
LEG-denmark

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)*(D2:D9=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)*(D2:D9=F$1+25))
--
David Biddulph

"LEG" wrote in message
...
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.

--
LEG-denmark

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


"David Biddulph" wrote:

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)*(D2:D9=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)*(D2:D9=F$1+25))
--
David Biddulph

"LEG" wrote in message
...
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.

--
LEG-denmark