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
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 |
#4
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 |
#5
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 |
#6
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 |
#7
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! |
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 |