Calculating time
 

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!

Calculating time
 

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


Donnas3944 wrote:

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

Calculating time
 

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



"Donnas3944" wrote:

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!

Calculating time
 

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" wrote:

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


Donnas3944 wrote:

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

Calculating time
 

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?

Donnas3944 wrote:

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" wrote:

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


Donnas3944 wrote:

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


--

Dave Peterson

Calculating time
 

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" wrote:

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?

Donnas3944 wrote:

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" wrote:

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


Donnas3944 wrote:

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


--

Dave Peterson

Calculating time
 

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

"Donnas3944" wrote in message
...
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" wrote:

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?

Donnas3944 wrote:

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" wrote:

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


Donnas3944 wrote:

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


--

Dave Peterson

Calculating time
 

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.


David Biddulph wrote:

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

"Donnas3944" wrote in message
...
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" wrote:

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?

Donnas3944 wrote:

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" wrote:

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


Donnas3944 wrote:

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


--

Dave Peterson


--

Dave Peterson

Calculating time
 

In the formula BAR.

(Stupid fingers)

Dave Peterson wrote:

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.

David Biddulph wrote:

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

"Donnas3944" wrote in message
...
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" wrote:

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?

Donnas3944 wrote:

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" wrote:

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


Donnas3944 wrote:

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


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Calculating time
 

Thanks! You have been extremly helpful!

"Dave Peterson" wrote:

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.


David Biddulph wrote:

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

"Donnas3944" wrote in message
...
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" wrote:

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?

Donnas3944 wrote:

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" wrote:

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


Donnas3944 wrote:

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


--

Dave Peterson


--

Dave Peterson