Calculating time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can not figure out how to do the following:

I am trying to calculate the amount a time someone spends on the phone
making calls. Let's say they made 99 calls, average call lasts 1 minute and
21 seconds and they sat 8 appointments.

When I enter 1:21 and format the cell as mm:ss is appears as 21:00 and in
the formula line it shows as 1:21:00 AM. When I enter the formula to figure
the amount of time was spent on the phone I used B6*E6 (calls made x avg
call time)and my total time appears like 13:39:00. See below.

Date Calls Made Appt Set Avg Call Time Total Time Spent on Phone
24-Apr 99 8 21:00 13:39:00

I know I am missing something simple but can not figure this out. I would
appreciate any help anyone can offer. This is making me crazy!

Thanks!
 
If you see 1:21:00 AM in the formula bar, then you're entering 1 hour, 21
minutes, 0 seconds.

Try entering:
0:1:21

If you have lots of data to fix, you can try this:
type 60 into an empty cell
edit|copy that cell
select the range to fix
edit|Paste special|check divide

Clean up that cell with 60 in it.

You may have to reformat those cells, too (mm:ss).
 
You need to enter times including the hours even if hours are zero and
irrespective of how you format the cell. eg 0:1:20 is 1 minute 20 seconds. If
you leave out the zero then it becomes 1 hour 20 minutes.

Regards,

OssieMac
 
I entered it as you suggested formatting the cell using mm:ss. Once I
leave the cell it reverts back to 01:21 and the formula bar still shows
12:01:21 am. Any other suggestions????
 
Ignore the formula bar (this time). That's the way excel shows time in the
formula bar.

I bet your cell with the formula worked ok though, right?
 
Yes it did. My only concern now is that it still looks like 1:21 in the
cell, I don't want this to cause confusion with the people who will be
analizing the report. I really would like for the time to look as you
suggested I enter it...ie. 0:1:21. At least it is calculating correctly!

Thanks a million for your help.
 
If you want it to display as 0:1:21, format as h:m:ss
--
David Biddulph

Donnas3944 said:
Yes it did. My only concern now is that it still looks like 1:21 in the
cell, I don't want this to cause confusion with the people who will be
analizing the report. I really would like for the time to look as you
suggested I enter it...ie. 0:1:21. At least it is calculating correctly!

Thanks a million for your help.
Click to expand...
 
I think that Donnas3944 meant the way the value looked in the formula, not the
cell.

But the original poster could suggest this to the users:

Change the windows short date format to 24 hour format--don't include the
AM/PM. But this is a user by user setting.

Open up the regional settings applet under control panel

In winXP:
windows start button|settings|control panel|regional and language options
On the Regional Options tab
click the Customize button
Then go to the Time tab and select one of the 24 hour format options.
 
In the formula BAR.

(Stupid fingers)

Dave said:
I think that Donnas3944 meant the way the value looked in the formula, not the
cell.

But the original poster could suggest this to the users:

Change the windows short date format to 24 hour format--don't include the
AM/PM. But this is a user by user setting.

Open up the regional settings applet under control panel

In winXP:
windows start button|settings|control panel|regional and language options
On the Regional Options tab
click the Customize button
Then go to the Time tab and select one of the 24 hour format options.
Click to expand...
 
Thanks! You have been extremly helpful!

Dave Peterson said:
I think that Donnas3944 meant the way the value looked in the formula, not the
cell.

But the original poster could suggest this to the users:

Change the windows short date format to 24 hour format--don't include the
AM/PM. But this is a user by user setting.

Open up the regional settings applet under control panel

In winXP:
windows start button|settings|control panel|regional and language options
On the Regional Options tab
click the Customize button
Then go to the Time tab and select one of the 24 hour format options.
Click to expand...
 
Back
Top