Display running average of hours/minutes

[Nu2Java]

Hello, I have a formula here that does not seem to be working very well for showing an average time for the previous day. I am simply adding a start time in column A and finish time in column B. As this data is added during the course of a day, I want to display a running average as this happens to see if jobs are getting completed slower or faster. This is the formula i am using for the average, it seems to work ok for the current day, but the previous day is off like 30-45 minutes.

Code for TODAY:

Please Login or Register  to view this content.

Code for Previous Day:

Please Login or Register  to view this content.

I am using this for to cover the first 100 rows for now, but maybe there is even a better formula to display a running average. Any help is greatly appreciated.

[protonLeah]

">=" TODAY()-1, spans yesterday and today; therefore:

Please Login or Register  to view this content.

[Nu2Java]

protonLeah... thanks for the help. The formula now gives me #DIV/0! error in the cell. Not sure what is happening there.

[Nu2Java]

I wanted to make sure I am as specific as possible as to what I am doing.... Based on the formula, Column D2:D100 is the Date&Time the job was finished. Column E2:E100 is the time it took to finish formatted h:mm.

[protonLeah]

You should upload a workbook. That error indicates that there were none for the previous day.

[Nu2Java]

Thanks for any help with the file attached... I am completely stuck on this and cannot figure out why the previous day will not return the correct results. Today seems to average just fine.

[Nu2Java]

Someone? Please help....

[Nu2Java]

Can someone figure this out? I feel I am asking the impossible. Any help would be greatly appreciated.

[protonLeah]

What are you calculating in column E?

[Nu2Java]

ProtonLeah... I am calculating the average time of the job from start to finish between the hours of 7am to 4pm.

[protonLeah]

Since Today() returns an integer and your dates include times (decimals), for previous day, try:

Please Login or Register  to view this content.

[Nu2Java]

ProtonLeah... I can't thank you enough, this works great. I appreciate your time and help.