Trouble with absolute values and hours/minutes and formulas

[dglazer]

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

[David A Coop]

Welcome to the forum dglazer,

Your problem is that you are working with different UNITS.

($D$3-(B4+C4)) are all days so you have to multiply by 24 to get hours.

So. =E4/(($D$3-(B4+C4))*24)

This does seem to me to be an unusual calculation! Should you be basing it on working hours? If so, =E4/(($D$3-(B4+C4))*8) would be more appropriate.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.