Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
I have 2,300 entries of time. Column A is beginning time (23:30:48) and
Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
use:
=Mod(B1-A1,1) to get value in column C Format your Total cell as [hh]:mm:ss "Leland7" wrote: I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
Use:
=MOD(B1-A1,1) or: =B2-A2+(B2<A2) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Leland7" wrote in message ... I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not the answer of 1128:35:20. Can you assist? Thank you! Col A Col B Col C Begin End Elapsed 18:21:52 19:24:33 1:02:41 22:16:08 06:36:37 8:20:29 19:56:01 00:44:31 4:48:30 11:49:14 17:13:53 5:24:39 12:01:04 13:08:31 1:07:27 13:47:44 16:30:08 2:42:24 15:33:57 16:33:39 0:59:42 18:12:49 18:22:17 0:09:28 Total Elapsed 1128:35:20 "Toppers" wrote: use: =Mod(B1-A1,1) to get value in column C Format your Total cell as [hh]:mm:ss "Leland7" wrote: I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
The formula for Column C works out fine, but the Grand Total column isn't
giving the correct answer. The total hours should be about 24+ hours, not the answer of 1128:35:20. Can you assist? Thank you! Col A Col B Col C Begin End Elapsed 18:21:52 19:24:33 1:02:41 22:16:08 06:36:37 8:20:29 19:56:01 00:44:31 4:48:30 11:49:14 17:13:53 5:24:39 12:01:04 13:08:31 1:07:27 13:47:44 16:30:08 2:42:24 15:33:57 16:33:39 0:59:42 18:12:49 18:22:17 0:09:28 Total Elapsed 1128:35:20 "Sandy Mann" wrote: Use: =MOD(B1-A1,1) or: =B2-A2+(B2<A2) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Leland7" wrote in message ... I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
You are in error by exactly 46 whole days, so my guess is that some of your
input data has date information which you are not seeing (and which you may not be intending to use), together with the times. Format each of the cells in column C as [h]:mm:ss so that you can get a clue where it is going wrong. I assume that you didn't take Toppers advice to use =Mod(B1-A1,1) , as that would have thrown away the extra days that are troubling you. To see exactly what is in your input data, temporarily reformat columns A and B using a custom format such as dd mmm yyyy hh:mm:ss -- David Biddulph "Leland7" wrote in message ... The formula for Column C works out fine, but the Grand Total column isn't giving the correct answer. The total hours should be about 24+ hours, not the answer of 1128:35:20. Can you assist? Thank you! Col A Col B Col C Begin End Elapsed 18:21:52 19:24:33 1:02:41 22:16:08 06:36:37 8:20:29 19:56:01 00:44:31 4:48:30 11:49:14 17:13:53 5:24:39 12:01:04 13:08:31 1:07:27 13:47:44 16:30:08 2:42:24 15:33:57 16:33:39 0:59:42 18:12:49 18:22:17 0:09:28 Total Elapsed 1128:35:20 "Toppers" wrote: use: =Mod(B1-A1,1) to get value in column C Format your Total cell as [hh]:mm:ss "Leland7" wrote: I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
My sum comes to 24:35:20 ... how do you calculate it?
"Leland7" wrote: The formula for Column C works out fine, but the Grand Total column isn't giving the correct answer. The total hours should be about 24+ hours, not the answer of 1128:35:20. Can you assist? Thank you! Col A Col B Col C Begin End Elapsed 18:21:52 19:24:33 1:02:41 22:16:08 06:36:37 8:20:29 19:56:01 00:44:31 4:48:30 11:49:14 17:13:53 5:24:39 12:01:04 13:08:31 1:07:27 13:47:44 16:30:08 2:42:24 15:33:57 16:33:39 0:59:42 18:12:49 18:22:17 0:09:28 Total Elapsed 1128:35:20 "Toppers" wrote: use: =Mod(B1-A1,1) to get value in column C Format your Total cell as [hh]:mm:ss "Leland7" wrote: I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
I redid all the cells formats and formulas to Toppers advice and it worked.
Thank you very much! "David Biddulph" wrote: You are in error by exactly 46 whole days, so my guess is that some of your input data has date information which you are not seeing (and which you may not be intending to use), together with the times. Format each of the cells in column C as [h]:mm:ss so that you can get a clue where it is going wrong. I assume that you didn't take Toppers advice to use =Mod(B1-A1,1) , as that would have thrown away the extra days that are troubling you. To see exactly what is in your input data, temporarily reformat columns A and B using a custom format such as dd mmm yyyy hh:mm:ss -- David Biddulph "Leland7" wrote in message ... The formula for Column C works out fine, but the Grand Total column isn't giving the correct answer. The total hours should be about 24+ hours, not the answer of 1128:35:20. Can you assist? Thank you! Col A Col B Col C Begin End Elapsed 18:21:52 19:24:33 1:02:41 22:16:08 06:36:37 8:20:29 19:56:01 00:44:31 4:48:30 11:49:14 17:13:53 5:24:39 12:01:04 13:08:31 1:07:27 13:47:44 16:30:08 2:42:24 15:33:57 16:33:39 0:59:42 18:12:49 18:22:17 0:09:28 Total Elapsed 1128:35:20 "Toppers" wrote: use: =Mod(B1-A1,1) to get value in column C Format your Total cell as [hh]:mm:ss "Leland7" wrote: I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for elapsed time + grand total
I had some cells that weren't formatted as you first said and once I changed
them, it worked. Thank you very much for your help! "Toppers" wrote: My sum comes to 24:35:20 ... how do you calculate it? "Leland7" wrote: The formula for Column C works out fine, but the Grand Total column isn't giving the correct answer. The total hours should be about 24+ hours, not the answer of 1128:35:20. Can you assist? Thank you! Col A Col B Col C Begin End Elapsed 18:21:52 19:24:33 1:02:41 22:16:08 06:36:37 8:20:29 19:56:01 00:44:31 4:48:30 11:49:14 17:13:53 5:24:39 12:01:04 13:08:31 1:07:27 13:47:44 16:30:08 2:42:24 15:33:57 16:33:39 0:59:42 18:12:49 18:22:17 0:09:28 Total Elapsed 1128:35:20 "Toppers" wrote: use: =Mod(B1-A1,1) to get value in column C Format your Total cell as [hh]:mm:ss "Leland7" wrote: I have 2,300 entries of time. Column A is beginning time (23:30:48) and Column B is ending time (01:18:24). I need to calculated elapsed time in Column C and then add the sum of Column C to determine Average elapsed time of the 2,300 entries. I have columns A B & C formated [h]:mm:ss. Problem is fine until start before midnight and end after, the formula doesn't work. Also, adding down column C for a grand total, doesn't work. Appears to not want to go past 24. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grand total time between two total cells | Excel Discussion (Misc queries) | |||
Old Lotus Sub Total & Grand Total formula | Excel Discussion (Misc queries) | |||
time elapsed formula | Excel Worksheet Functions | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
Custom formula to show each item as percentage of grand total? | Charts and Charting in Excel |