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! :)