Hi all. I'm new here and found this site trying to Google the answer.
I'm looking for assistance with a time sheet. I want to do a sum which will tally up the total hours/minutes of each night on call, but it's not quite that simple.
To explain; from the minute we receive a call to the minute it's resolved we are paid for our time.
Now sometimes a call will go on for hours and whilst the clock is ticking we can be sat doing nothing whilst on other people. In these periods we should answer any other waiting calls, but if they're handled within the window of that first call we wouldn't be paid for these additional calls because the initial call would cover our time.
For example
Lines 3-5 are regular calls in succession, they are easy to calculate and add up.
Line 6 in red is where it get's tricky. That call runs for an hour, but in between I answer a second call seen on line 7 in purple.
Now in the total box seen in F1 the sum =SUM(F3:F7) is no good here because it cannot intelligently understand that line 7 should be disregarded as the time period was being covered already by line 6
So if indeed possible, how can I made the total in F9 smarter so that it looks at the times and establishes the total based on the data rather than just simple additions? I need Excel to look at data and recognises blocks where call outs are combined
I'm looking for assistance with a time sheet. I want to do a sum which will tally up the total hours/minutes of each night on call, but it's not quite that simple.
To explain; from the minute we receive a call to the minute it's resolved we are paid for our time.
Now sometimes a call will go on for hours and whilst the clock is ticking we can be sat doing nothing whilst on other people. In these periods we should answer any other waiting calls, but if they're handled within the window of that first call we wouldn't be paid for these additional calls because the initial call would cover our time.
For example
Lines 3-5 are regular calls in succession, they are easy to calculate and add up.
Line 6 in red is where it get's tricky. That call runs for an hour, but in between I answer a second call seen on line 7 in purple.
Now in the total box seen in F1 the sum =SUM(F3:F7) is no good here because it cannot intelligently understand that line 7 should be disregarded as the time period was being covered already by line 6
So if indeed possible, how can I made the total in F9 smarter so that it looks at the times and establishes the total based on the data rather than just simple additions? I need Excel to look at data and recognises blocks where call outs are combined
