Hi,

I have been tring to write this formular all day. I need help to write
the formular to do this?

attached is a file which displays what i am trying to do.

What I need the formular to do is read the info from table a to read
if, the date and time match it should add all the entrys and fill in
the space with the number and the shift lenght is for example 5hours
from 16:00 then it should add 1 entry for 5 hours from 16:00 to 21:00.

Please help. If you require more info please post a response.
Thanks
Brent


+-------------------------------------------------------------------+
|Filename: shift query.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4911 |
+-------------------------------------------------------------------+

--
Brento
------------------------------------------------------------------------
Brento's Profile: http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=553789

Yep, not very clear.

Brento Wrote:
it should add all the entrys
All what entries?

and fill in the space with the number
What number?

and the shift lenght is for example 5hours from 16:00
I can't see any shifts starting at 16:00 or for 5 hours

then it should add 1 entry for 5 hours from 16:00 to 21:00.
How? The table ends at 20:00. How would it add a new entry?


How about you post an example in Excel, showing exact examples with
actual before and after data.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=553789

I have updated the attachement and given 2 examples of what I am trying
to acheive. Please take a look and let me know if more info is needed.

thanks
Brent


+-------------------------------------------------------------------+
|Filename: shift query.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4913 |
+-------------------------------------------------------------------+

--
Brento
------------------------------------------------------------------------
Brento's Profile: http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=553789

With Table A in A1:C6 try:

=SUMPRODUCT(($A$2:$A$6=G$1)*($B$2:$B$6<=$F2)*($B$2 :$B$6+$C$2:$C$6$F2))

It returns 0 if no match if that is a problem then uncheck zero values in
Tools Options View


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Brento" wrote in
message ...

I have updated the attachement and given 2 examples of what I am trying
to acheive. Please take a look and let me know if more info is needed.

thanks
Brent


+-------------------------------------------------------------------+
|Filename: shift query.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4913 |
+-------------------------------------------------------------------+

--
Brento
------------------------------------------------------------------------
Brento's Profile:
http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=553789

You Are A God......it Is Exactly What I Was Looking For I Can Now Sleep
Again.

Thanks
Brent:) :) :) :)


--
Brento
------------------------------------------------------------------------
Brento's Profile: http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=553789

You're welcome - hope that you are now well rested.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Brento" wrote in
message ...

You Are A God......it Is Exactly What I Was Looking For I Can Now Sleep
Again.

Thanks
Brent:) :) :) :)


--
Brento
------------------------------------------------------------------------
Brento's Profile:
http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=553789