rolling 60 minutes

[thomasleitner]

hej all,


i have a issue which i need help with.


i have two collums - collum A are the unit produced and collum B the time when it was produced. see sample file - the real file has 70000+ entries

https://docs.google.com/leaf?id=0B4cakkS8jEm9ODNkNGFkODUtODAzZS00MDdiLWFmZWYtNTk5ODAyYzdhNTZj&sort=name&layout=list&num=50


i need to find the units produced from the time produced and 60 minutes on... that i need for all rows so that i can see when i have the highest production.

 

[Hui]

Thomas

Please check your upload

Google error: "Sorry, the page (or document) you have requested is not available."

 

[thomasleitner]

sorry dont know what went wrong try this link


https://docs.google.com/leaf?id=0B4cakkS8jEm9ODNkNGFkODUtODAzZS00MDdiLWFmZWYtNTk5ODAyYzdhNTZj&sort=name&layout=list&num=50

 

[Hui]

When you say the units produced from the time

What do exactly mean?

do you mean Units/(t2-t1) ?


Do you realise your date/times are all text or is that a Google Docs thing?

 

[thomasleitner]

no the units produced are at the time in the same row..


so 2010/09/01 05:04:00 (which seems to be a text unfortunatly-- so i need to convert it to a time i guess) i produced 0 units..

2010/09/01 06:00:00 i produced 143 unit...


i need to know from the time 2010/09/01 05:04:00 and 60 minutes on how many unit have been produced. then i need to know the dame form the nest row and so on...

 

[Hui]

Thomas

Try:

C2: =TIME(MID(B2,13,2),MID(B2,16,2),RIGHT(B2,2))

D2: =SUMPRODUCT(1*($C$2:$C$37>=C2)*($C$2:$C$37<=C2+(1/24)),$A$2:$A$37)


Copy both down


This gives me:

6:00am 843 units

7:01am 2466 units

 

[thomasleitner]

thx.. works like a charm.. except i had to use to helper collums to get the time correct