Hi everybody
I have a list of Names and dates in col A and start and end times in col b
and c and Names in col Q, I have worked out this
"=SUM(IF($A$1:$A$13562=$Q$4,$C$1:$C$13562-$B$1:$B$13562))" to find out how
long we have spent at a given name down the whole of col a, I would like to
be able to set a stert date and end date so that it will only return the time
spent at a given name between the dates I enter????
eg Col a b c q r
31/03/05
Baker 06:00 06:30 Baker 00:30
Smith 07:00 08:00 Smith 03:00
Jones
00:10
05/04/05
Smith 08:00 09:00
Jones 10:50 11:00

06/04/05
Smith 08:00 08:30

07/04/05
Smith 08:00 08:30
How could I sum the times in b & c between say 05/04/05 and 07/04/05 ???
Thanks in advance

You need to lay the data out differently, getting the date is too hard with
that layout.

For instance, if you added this formula to D2

=IF(A2="","",IF(ISNUMBER(A1),A1,D1))

and copied it down, you could just use

=SUMPRODUCT(--($A$1:$A$1352=$Q$4),--(D1:D1352=--"2005-04-05"),--(D1:D1352<=--"2005-04-07"),$C$1:$C$1352-$B$1:$B$1352)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mlearner" wrote in message
...
Hi everybody
I have a list of Names and dates in col A and start and end times in col b
and c and Names in col Q, I have worked out this
"=SUM(IF($A$1:$A$13562=$Q$4,$C$1:$C$13562-$B$1:$B$13562))" to find out how
long we have spent at a given name down the whole of col a, I would like
to
be able to set a stert date and end date so that it will only return the
time
spent at a given name between the dates I enter????
eg Col a b c q r
31/03/05
Baker 06:00 06:30 Baker 00:30
Smith 07:00 08:00 Smith 03:00
Jones
00:10
05/04/05
Smith 08:00 09:00
Jones 10:50 11:00

06/04/05
Smith 08:00 08:30

07/04/05
Smith 08:00 08:30
How could I sum the times in b & c between say 05/04/05 and 07/04/05 ???
Thanks in advance

thanks Bob I will give it a try, the data is imported from another database
so I have no control over its layout or format and it is a big lump of data
so the less I have to move or adjust the quicker my worksheet will run.

"Bob Phillips" wrote:

You need to lay the data out differently, getting the date is too hard with
that layout.

For instance, if you added this formula to D2

=IF(A2="","",IF(ISNUMBER(A1),A1,D1))

and copied it down, you could just use

=SUMPRODUCT(--($A$1:$A$1352=$Q$4),--(D1:D1352=--"2005-04-05"),--(D1:D1352<=--"2005-04-07"),$C$1:$C$1352-$B$1:$B$1352)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mlearner" wrote in message
...
Hi everybody
I have a list of Names and dates in col A and start and end times in col b
and c and Names in col Q, I have worked out this
"=SUM(IF($A$1:$A$13562=$Q$4,$C$1:$C$13562-$B$1:$B$13562))" to find out how
long we have spent at a given name down the whole of col a, I would like
to
be able to set a stert date and end date so that it will only return the
time
spent at a given name between the dates I enter????
eg Col a b c q r
31/03/05
Baker 06:00 06:30 Baker 00:30
Smith 07:00 08:00 Smith 03:00
Jones
00:10
05/04/05
Smith 08:00 09:00
Jones 10:50 11:00

06/04/05
Smith 08:00 08:30

07/04/05
Smith 08:00 08:30
How could I sum the times in b & c between say 05/04/05 and 07/04/05 ???
Thanks in advance