J
Jan
Thanks to Bob for help with the sumproduct formula
This is what I have
=SUMPRODUCT((C$1>=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4
Although my actual spreadsheet is a variation of above, I have recreated as posted and I can get the first hour to fill in if it is a whole hour. However the run is longer than an hour or a portion of an hour I only get a whole hours value in the starting hour.
C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29 =SUMPRODUCT((C$1>=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4
I get 100 in C7 (2:00) and should be 50 and all other hours are empty.
Thanks again
Jan
This is what I have
based on start time and stop time. What formula would work for me?Hi to all
This is my basic spreadsheet
C1 Production Start time
C2 Produstion Stop time
C3 Run time (C2-C1)
C4 Items per Hour
C5 Total production (C3*C4)
C6-C29 (24 hours of the day) I would like the items produced that hour
=SUMPRODUCT((C$1>=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4
Although my actual spreadsheet is a variation of above, I have recreated as posted and I can get the first hour to fill in if it is a whole hour. However the run is longer than an hour or a portion of an hour I only get a whole hours value in the starting hour.
C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29 =SUMPRODUCT((C$1>=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4
I get 100 in C7 (2:00) and should be 50 and all other hours are empty.
Thanks again
Jan