Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating time and negative time | Excel Worksheet Functions | |||
calculating time | Excel Discussion (Misc queries) | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions |