Hello Experts,

I've attached the file I'm working on. Please bare with me as I explain my problem.

The formula I am having trouble with G12

And more specifically, the part of the formula that is causing me trouble is:

SUM(CallLog)/SUM(End-Start

-Exclude)*60

As of right now, the formula returns the correct value.

It calculates average minutes talked per hour.

IE: If you started at 12, and ended at 5 = You worked a total of 5 hours.

If you made 4, 30 minute phone calls - you talked for a total of 2 hours, out of the 5 hours you were at work. Therefor, you averaged 24 minutes per hour of talk time.

Hopefully, I haven't lost you...

Now, this formula is great - but it has one major flaw. This calculation is based off of the total time you're on the clock.

For example, If I started at 12pm, made 1 phone call which lasted 24 minutes, and the current time is 12:24 pm, then my average talk time per hour SHOULD BE 24 minutes. But, with the current formula, it would report something lower cause it's looking at the end time.

If the time is currently after the end time, then yes calculate like normal. But if I'm still working during my shift, then the calculation should be looking at NOW(), not end.

(I previously had a EE member help me through this, taking into account the NOW() - but I've since updated the spreadsheet from scratch and I'm basically starting over. I attempted to take the previous answer and basically change up the references, but none of the results were what I was expecting. So I figured it was best to put back in the basic formula, and post the workbook here.

If you have any questions, just ask - I plan on staying up a lil longer. Kinda want to write this off my list before I head to bed. :)

Hopefully that made sense...

Thank you in advance!

~ Geekamo

Workbook2.xlsm
The now time is LESS than the START time so excel sees that you have not started work yet and the formula is subtracting a smaller time from a larger time

0055 hrs - 1200 hrs

which will give a negative result.

One way to solve this is to enter the working date in a cell (mannually; not the formula today()) and subtract the cell from now; instead of now()-today()