picking out date ranges
 

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

picking out date ranges
 

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

picking out date ranges
 

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