Calculating time

[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!

 

[Dave Peterson]

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).

 

[Guest]

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

 

[Guest]

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????

 

[Dave Peterson]

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?

 

[Guest]

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.

 

[David Biddulph]

If you want it to display as 0:1:21, format as h:m:ss
--
David Biddulph

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.

 

[Dave Peterson]

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.

 

[Dave Peterson]

In the formula BAR.

(Stupid fingers)

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.

 

[Guest]

Thanks! You have been extremly helpful!

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.