More Formula Help....
 

Thanks to Bob Phillips for the following formul

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$

it is not working quite right yet

This is what I hav
Start C1=00:00 (entered
Stop C2=5:00 (entered
Duration C2-C1 (Calculated
Items per Hour C4 (Entered
Total Production Production Total C
B6:B29 1-24 (Hours in day
C6:C29 formula=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4 (Copied down the column

I would expect there to be 100 in C6:C10 but Im only getting it in C6. Also if start time is anything but even hour, it still gives full production for the hour. Any ideas suggestions

Thanks again!

More Formula Help....
 

This formula does what you describe: (adjusted for revelation that hours are
in column B - assumes hours are stored as times (ex: 1:00) and not as
integers (ex: 1)
Place this in C6 and drag fill down.
=MAX(0,((MIN(B6+TIMEVALUE("01:00"),$C$2)-MAX(B6,$C$1))*24/$C$3)*$C$5)

I guess if you only want to work with Bob, then you can ignore it again.

--
Regards,
Tom Ogilvy


"Jan" wrote in message
...
Thanks to Bob Phillips for the following formula

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

it is not working quite right yet.

This is what I have
Start C1=00:00 (entered)
Stop C2=5:00 (entered)
Duration C2-C1 (Calculated)
Items per Hour C4 (Entered)
Total Production Production Total C5
B6:B29 1-24 (Hours in day)
C6:C29
formula=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4
(Copied down the column)

I would expect there to be 100 in C6:C10 but Im only getting it in C6.
Also if start time is anything but even hour, it still gives full production
for the hour. Any ideas suggestions?

Thanks again!

More Formula Help....
 

Indeed.

Perhaps Jan, you are not receiving all of the posts?

The formula which I recommended works fine, with a little modification. The
original formula is in the "Hours affected by Dates" page of my website.

Here's the modification that fits your problem.
Just put 00:00 in D6, =D6+1/24 in D7
This formula (no spaces) goes in C6.
=IF(OR(AND(C$1<=C$2,C$2<=D6),AND(C$1=E6,OR(C$1<=C $2,C$2<=D6))),0,IF(AND(C$2
<=E6,C$2=D6,OR(C$1<=D6,C$1=E6)),C$2-D6,IF(OR(AND(C$1<=D6,C$2=E6),AND(C$1
=C$2,OR(C$1<=D6,C$2=E6))),E6-D6,IF(AND(C$1<=C$2,C$1=D6,C$2<=E6),C$2-C$1,IF
(AND(C$1=D6,C$1<=E6,OR(C$2<=D6,C$2=E6)),E6-C$1,(C$2-D6)+(E6-C$1))))))

Then fill down the formula.

It's overkill (because it handles ranges over midnight), but should do what
you're after.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Tom Ogilvy" wrote in message
...
This formula does what you describe: (adjusted for revelation that hours
are
in column B - assumes hours are stored as times (ex: 1:00) and not as
integers (ex: 1)
Place this in C6 and drag fill down.
=MAX(0,((MIN(B6+TIMEVALUE("01:00"),$C$2)-MAX(B6,$C$1))*24/$C$3)*$C$5)

I guess if you only want to work with Bob, then you can ignore it again.

--
Regards,
Tom Ogilvy


"Jan" wrote in message
...
Thanks to Bob Phillips for the following formula

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

it is not working quite right yet.

This is what I have
Start C1=00:00 (entered)
Stop C2=5:00 (entered)
Duration C2-C1 (Calculated)
Items per Hour C4 (Entered)
Total Production Production Total C5
B6:B29 1-24 (Hours in day)
C6:C29

formula=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4
(Copied down the column)

I would expect there to be 100 in C6:C10 but Im only getting it in C6.
Also if start time is anything but even hour, it still gives full
production
for the hour. Any ideas suggestions?

Thanks again!