asked on
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 18 Comments.
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 18 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.