Help Me !!!!

[Brento]

Hi,

I have been tring to write this formular all day. I need help to writ
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 rea
if, the date and time match it should add all the entrys and fill i
the space with the number and the shift lenght is for example 5hour
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
Bren

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

 

[Mallycat]

Yep, not very clear.

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

 

[Brento]

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

thanks
Bren

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Brento]

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

Thanks
Brent :

 

[Sandy Mann]

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

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk