Determining required number of contacts

**Bobcalling** · 01-10-2007, 01:15 AM

I am having trouble with determining the required number of contacts using an [h]:mm time format.

The spreadsheet is as follows: AE10 is time out of service, W10 is number of contacts made, AC10 is required number of contacts, AD10 is number above or below, B10 is hours worked.

My employees are required to have one sales contact for every two hours of uncommitted time.

I have the following formulas:
AC10 is =(B10-AE10)*0.5
AD10 is =W10-AC10 When I use an [h]:mm time format, I the answer on AD10 is always +1 or +2. If I use the same formula in decimal form the answer comes out showing the correct number of contacts above or below the required number of contacts. My problem is that the spreadsheet has to be in the [h]:mm time format.

I would appreciate any help on the formula to use to get the correct answer.

Thanks for your help

Bob

**Dav** · 01-10-2007, 04:07 AM

Times multimplied by 24 give you hours, as they are stored as 1 being a day

well in terms of the answer, if someone is uncomiited for 90 minutes, shuld they have 0.75 contacts or 0 contacts, or 1 contact?

Try if in the above you would have 0.75 contacts
AC10 is =(B10-AE10)*0.5*24

Try if in the above you would have 0 contacts
AC10 is =trunc((B10-AE10)*0.5*24)

Try if in the above you would have 1 contacts
AC10 is =round((B10-AE10)*0.5*24,0)

AD10 is =W10-AC10 remains unchanged

Does that work? I fnot you must tell us if the quantities for each cell are formatted number or a time. I have assumed that hours worked is a time, as is time out of service.

Regards

Dav