Calculating total minutes worked in real time, minus breaks and lunch.

You're problem may be that the values in E are the result of the NOW() funciton.
The NOW funciton includes the DATE as well as the time.
So E5 will always be greater than TIME(11,0,0) because it's including today's date.

Try changing all the references to E5 to MOD(E5,1)

Or better yet, change the formula in E to
=MOD(NOW(),1)

Thanks Jonmo1!

It's now working, well almost. How do I convert it from hh:mm to just minutes. I have attached the updated file but it currently just shows hours and minutes (5:56) I need it to read 356 for total minutes.

Never mind I think I figured it out. Made it a general number then =E3*1440.

You're welcome.

Thanks again for your help. I have attached a sample file using what you showed me to get my results. If you can think of an easier way to do this please let me know but this defiantly seems to be working.

I don't see where you implimented my suggestion?

Ooops. Uploaded the wrong file.

Before getting into this (I'm leaving for the day anyway)..

Are you SURE that formula is giving you the results you want?
I don't understand the relationship between the current time and the average time between calls that have already occurred?
If you look at the document at say 3PM, it will have different results than it did when you looked at it at 8AM.

I don't get it.

And what about times Less than 2AM?
Time between Midnight and 2AM are not accounted for.


Best advice to begin with is to get rid of the whole current time column.
And have just a single cell, say M1 or whatever contain the =MOD(NOW(),1)
Then make the formulas in E refer to $M$1 instead of F2

NOW is a volatile funciton, and repeating it over and over creates alot of unnecessary work for Excel.

Jonmo you were right. I checked it this morning and it does not work.

To help you understand what I'm looking for. Where I work everyone starts work at 7:30 AM. We have 15 minute breaks at 9:00 AM & 2:00 PM. We also have a 1 hour lunch at 11:00 AM.

Since we are an outbound sales call center we currently have a dashboard that tracks number of calls and minutes spent on the phone and updates constantly. I'm looking to find out the average amount of time each rep spends in between making phone calls in real time. The only way to do so you have to know exactly how many minutes have currently been worked. The formula is = (Total Minutes @ Work - Minutes Spent on the Phone)/# of Calls. Since I need the most up to the date minutes worked I need a way to subtract those minutes as breaks and lunch occur.

I'm not sure if I need to approach this from a different angle but I would like to just drop data into excel throughout the day and have a function that works based on current time.

You can't base this on the "current time"
What you need is a cell that indicates the time that the report was generated.

Does EVERYONE take their breaks/lunches at exactly the same time?
That doesnt' make sense for a Call Center (I've worked at call centers too)..
So I don't think you can subtract 15 minutes simply because the report was generated after 9 AM.

You need to know what time each individual actually took their breaks/lunch

Everyone takes lunch and break at the same time in our largest and main office in Dallas. We do have an office in Utah that takes lunch at a different time but I am not worried about them.

So as I mentioned earlier there are 3 trigger times. 9:00, 11:00, & 2:00.

accidental re-post.

OK, so 2 basic problems then.

You can't refer to the "Current Time" using the NOW() function as stated before, the results will be different depending on what time of day you look at the report.
You need to make a cell that represents a 'static' time that the report was generated, say $M$1 for example.
And all the references in your formula should refer to $M$1 instead of the column F where you had the Now function.

2. Nested IF's are read from Left to Right.
The first one to be true is executed, the rest ignored.
So you need to test for the specific times in an appropriate order.
Your current formula is testing for >2AM then >11AM then >10AM
If the value is say 3PM, then the first IF (>2AM) is True, when it seems you actually intended for it to read the >11AM

So the order of the tests should go Largest to Smallest / Left to Right
>11AM then >10AM then >2AM

Hope that helps.