More Formula Help....

[Guest]

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!

 

[Tom Ogilvy]

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

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?

 

[Rob van Gelder]

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.