We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Call Duration Calculator
Posted on 2012-12-29
Hello Experts!
A requirement of my job is that I need to be on the phone for 0:24 minutes, out of every hour that I am working. I decided to create a spreadsheet this weekend, in order to help me stay more focused in achieving my daily goal of 2:00 hours of talk time.
I am hoping someone can offer some help to me in creating the formulas I need.
Columns F through J, contain the duration of the call. IE "0:30:00" = half hour. Calls can range from only seconds to even over an hour long.
C7 = Shift start time
C8 = Shift end time
C9 = Time excluded from actual 'normal' phone time. IE If you're in a meeting for an hour, you would type "1:00:00", and that "hour" of time would not require phone time too.
C14 = Subtotal of all call durations
C15 = Talk Time still needed
C16 = % of Goal completed.
So, if I'm scheduled to work 12:00 PM ~ 5:00 PM, that would be 5 working hours. For every hour, I need 0:24 minutes of talk time. Therefor my 5 hour shift, would require me talking on the phone for 2 hours.
Any help you can offer with the formulas that are needed would be a huge help. I'm ready to pull my hair out! :)
Thanks,
Geekamo
TimeCalculation.xlsx
A requirement of my job is that I need to be on the phone for 0:24 minutes, out of every hour that I am working. I decided to create a spreadsheet this weekend, in order to help me stay more focused in achieving my daily goal of 2:00 hours of talk time.
I am hoping someone can offer some help to me in creating the formulas I need.
Columns F through J, contain the duration of the call. IE "0:30:00" = half hour. Calls can range from only seconds to even over an hour long.
C7 = Shift start time
C8 = Shift end time
C9 = Time excluded from actual 'normal' phone time. IE If you're in a meeting for an hour, you would type "1:00:00", and that "hour" of time would not require phone time too.
C14 = Subtotal of all call durations
C15 = Talk Time still needed
C16 = % of Goal completed.
So, if I'm scheduled to work 12:00 PM ~ 5:00 PM, that would be 5 working hours. For every hour, I need 0:24 minutes of talk time. Therefor my 5 hour shift, would require me talking on the phone for 2 hours.
Any help you can offer with the formulas that are needed would be a huge help. I'm ready to pull my hair out! :)
Thanks,
Geekamo
TimeCalculation.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
4
4- +1
18 Comments
please see the attached file , I hope that what you want
TimeCalculation-v1.xlsx
TimeCalculation-v1.xlsx
1. Do you mean that for each row of F to J represents the call duration for one hour?
2. If the talk time is 12 min for hour 1 but 36 min for hour 2, would the value for still needed is 12 min?
3. Is the percentage of goal calculated on hour-based (i.e. hours with target met / total hours)?
Please clarify.
2. If the talk time is 12 min for hour 1 but 36 min for hour 2, would the value for still needed is 12 min?
3. Is the percentage of goal calculated on hour-based (i.e. hours with target met / total hours)?
Please clarify.
Columns F through J - Each time I finish a call, I will be typing values into those cells. Any cell within columns F through J. Consider those columns just a large scratch pad area to randomly type in call durations, the row or column itself has no meaning.
I'm not sure where the 12 minutes is coming from? For every hour worked, 24 minutes of talk time is required.
So basically, I want a spreadsheet that I can input...
My start time
My end time
Call durations
And it will calculate, and answer these questions....
Subtotal - Would be to total sum of all call durations, so basically this is the only formula I do know. It would be "=SUM(F:J)"
Still Need - Would be my 2 hour target MINUS the call durations
Does that clarify things? I'm sorry for the confusion.
I'm not sure where the 12 minutes is coming from? For every hour worked, 24 minutes of talk time is required.
So basically, I want a spreadsheet that I can input...
My start time
My end time
Call durations
And it will calculate, and answer these questions....
Subtotal - Would be to total sum of all call durations, so basically this is the only formula I do know. It would be "=SUM(F:J)"
Still Need - Would be my 2 hour target MINUS the call durations
Does that clarify things? I'm sorry for the confusion.
Active 6 days ago
Your attached file requires just a couple of adjustments:
C10: =C8-C7-C9
C15: =MAX(C10-C14,0)
C10: =C8-C7-C9
C15: =MAX(C10-C14,0)
@honestman31
Yes, I did check it out. But it wasn't what I'm looking for. I think I didn't do a good job of clarifying what I needed.
Yes, I did check it out. But it wasn't what I'm looking for. I think I didn't do a good job of clarifying what I needed.
@ Geekamo, thanks for clarification. Please see attached. Thank you.
TimeCalculation-v2.xlsx
TimeCalculation-v2.xlsx
Geekamo , I don't know what you don't like about it ?
you will need to enter the time of calls Vertically in column "F"
C14 =SUM(F2:F200) , shows the sub-totla correctly ( you have up to 200 calls , , you can increase if you want to )
C15 is correct
C16 is correct
so what u don't like about it ?
you will need to enter the time of calls Vertically in column "F"
C14 =SUM(F2:F200) , shows the sub-totla correctly ( you have up to 200 calls , , you can increase if you want to )
C15 is correct
C16 is correct
so what u don't like about it ?
@ honestman31
It's not that I don't like your solution, it just doesn't exactly do what I was envisioning. And I think it's of my own fault, I didn't give the best explanation of how I wanted this spreadsheet to work.
It's not that I don't like your solution, it just doesn't exactly do what I was envisioning. And I think it's of my own fault, I didn't give the best explanation of how I wanted this spreadsheet to work.
A typo corrected. Please see attached revised version.
TimeCalculation-v2.xlsx
TimeCalculation-v2.xlsx
@ pkwan
Your solution / attachment - is exactly what I was looking for. I have changed the start & end times, exclude, call durations, etc - and it is returning the correct answers. There's only one slight thing I noticed,... the 100% never goes higher?
For example, on a day were I worked 5 hours, and I was on the phone for 3 hours, then my % of Goal would be over 100%.
Your solution / attachment - is exactly what I was looking for. I have changed the start & end times, exclude, call durations, etc - and it is returning the correct answers. There's only one slight thing I noticed,... the 100% never goes higher?
For example, on a day were I worked 5 hours, and I was on the phone for 3 hours, then my % of Goal would be over 100%.
@ pkwan
I will admit, when it comes to formulas - I am lost. As I step through each part of your formula, I "think" I understand what it's doing - what's really confusing me though is "MAX".
I know if =MAX(A1:A10) was used, it would return the MAX value in that range. But I don't understand it's use in your formula. Could you elaborate?
I will admit, when it comes to formulas - I am lost. As I step through each part of your formula, I "think" I understand what it's doing - what's really confusing me though is "MAX".
I know if =MAX(A1:A10) was used, it would return the MAX value in that range. But I don't understand it's use in your formula. Could you elaborate?
pkwan used MAX to avoid getting getting negative or ###### result ,, so if the "Still Need " time is ( negative , means you already done more calls than required ) it will show zero in Still Need
@Geekamo,
1) If the % of goal needs to be over 100%, just replace the formula in C16 with
=C14/((C8-C7-C9)*24/60)
2) honestman31 is correct. I used max to return whichever is the greater. If the value of the formula "(C8-C7-C9)*24/60-C14" is greater than 0, then returns its value. Otherwise, it will return 0. I used it because if your call durations are over the target, there is no point to have a negative value for "Still needed".
1) If the % of goal needs to be over 100%, just replace the formula in C16 with
=C14/((C8-C7-C9)*24/60)
2) honestman31 is correct. I used max to return whichever is the greater. If the value of the formula "(C8-C7-C9)*24/60-C14" is greater than 0, then returns its value. Otherwise, it will return 0. I used it because if your call durations are over the target, there is no point to have a negative value for "Still needed".
@ honestman31
Thanks for the explanation of MAX in the formula.
When I first started working on this spreadsheet, I had many "supporting" cells on another part of the spreadsheet. I can only write basic formulas, so usually when I want to get to an answer - I have to break it down into steps. After I was working on it for a while, I realized how many supporting cells I needed in order for me to get answers into the cells you guys were working on. And when I finally gave up and posted here, is when I ran into the problem of that cell reading negative values. That was the moment I gave up! lol
Thanks for the explanation of MAX in the formula.
When I first started working on this spreadsheet, I had many "supporting" cells on another part of the spreadsheet. I can only write basic formulas, so usually when I want to get to an answer - I have to break it down into steps. After I was working on it for a while, I realized how many supporting cells I needed in order for me to get answers into the cells you guys were working on. And when I finally gave up and posted here, is when I ran into the problem of that cell reading negative values. That was the moment I gave up! lol
@ pkwan
Thank you very much for taking the time in helping me. I am so excited and can't wait to officially start using it on Monday! And thank you for the revision of the % formula.
@ All
Thank you all for your input. It's appreciated!
~ Geekamo
Thank you very much for taking the time in helping me. I am so excited and can't wait to officially start using it on Monday! And thank you for the revision of the % formula.
@ All
Thank you all for your input. It's appreciated!
~ Geekamo
Featured Post
Ready to get started with anonymous questions today? It's easy! Learn more.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll
718 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.