Excel formula to calculate the average flow per hour

[PhAnT0M]

I have attached the excel sheet with sample data. I need more generalised formula for flows per hour and flows per day since the existing formula in the sheet has many drawbacks:
1. At the end it shows a negative value and cant avoid it (since the number of rows is not fixed, I get this data from database by using a macro)
2. If the flow at any particular time( in this case say 19032012 00:36 is missing) fails then the whole formula for flow per hour goes wrong

Can I have a formula which calculates flows per hour depending on the time ( in this case 00:06 to 00:51)



Thanks in advance

[ATLGator]

Use this for column E:
=DATE(MID(A2,5,4),MID(A2,3,2),LEFT(A2,2))+TIME(MID(A2,10,2),RIGHT(A2,2),0)

And use this for Column G:
=SUMPRODUCT(((DATE(MID(A2:A31,5,4),MID(A2:A31,3,2),LEFT(A2:A31,2))+TIME(MID(A2:A31,10,2),RIGHT(A2:A31,2),0))<DATE(MID(A2:A31,5,4),MID(A2:A31,3,2),LEFT(A2:A31,2))+TIME(1,RIGHT(0),0))*(B2:B31))

For the TIME formula (bolded), the 1 represents the hour of the day. You could also replace the 1 with ROW()-1, which will adjust itself to 1 in the second row and then count up as you drag it down.

[ATLGator]

Sorry, I meant the formula for Column G to be:
=SUMPRODUCT(((DATE(MID(A2:A31,5,4),MID(A2:A31,3,2),LEFT(A2:A31,2))+TIME(MID(A2:A31,10,2),RIGHT(A2:A31,2),0))<DATE(MID(A2:A31,5,4),MID(A2:A31,3,2),LEFT(A2:A31,2))+TIME(1,0,0))*(B2:B31))

Same comment applies from above though

[PhAnT0M]

The above formula is not working and giving me error

[PhAnT0M]

This is not giving me flows per hour after dragging the formula, but the entire flow till the specified cell