I am trying to put together a spreadsheet with call times for our inside sales team. I cannot get the formulas to work and know I am doing something wrong with the formatting of the dates. What I am trying to do (and I will attach the spreadsheet below) is:

Total talk time (right now is expressed as a number, which I know it shouldn't be) --so E4, should be hours and minutes, so 35 hours and 25 minutes

Days this month is an absolute value, so $D$3 in this example

subtract the days off and the meeting days off (so B4 and C4 together)



What I've tried to do is =E4/($D$3-(B4+C4)) to figure out the average talk time.

Does not work and I've tried a whole bunch of different formulations of it. I'm sure I don't have the duration time correct. Can anyone assist?

Thanks,

dglazer

Attached Files

Sample Call Centre tracking.xlsx (9.9 KB, 18 views)

Kindly Use below Formula


=(E4)/(($D$3)-B4-C4)


Raushan Mishra

Since 35.25 isn't in a recognised hh:mm format, you need to do some data conversion before you can get the correct result. Try:
=INT(E4)/($D$3-(B4+C4))+MOD(E4,1)/($D$3-(B4+C4))/60

RAUSHANM: Your formula is logically the same as the original one posted by dglazer and gives exactly the same erroneous result.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]