Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
A spreadsheeI that allows me to enter hours is not adding up correctly
in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of doing what it's doing? oh, btw, the code is this for the totals cell: =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K41) )/15,0)*15,0) Thanks! |
#2
|
|||
|
|||
Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
Wow! No need for that, if the values are time values you can just use
=ROUND(SUM(K16:K41)*96,0)/96 to round to the nearest quarter btw, time can only handle up to 24 hours -- Regards, Peo Sjoblom (No private emails please) "StargateFan" wrote in message ... A spreadsheeI that allows me to enter hours is not adding up correctly in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of doing what it's doing? oh, btw, the code is this for the totals cell: =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K41) )/15,0)*15,0) Thanks! |
#3
|
|||
|
|||
Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
On Sun, 16 Oct 2005 13:39:23 -0400, StargateFan
wrote: A spreadsheeI that allows me to enter hours is not adding up correctly in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of doing what it's doing? oh, btw, the code is this for the totals cell: =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K41 ))/15,0)*15,0) Thanks! The [h]:mm cell formatting so that totals don't go over 24 hours works but only when not rounding down. With the rounding down code of =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K41) )/15,0)*15,0) rather than just =SUM(K16:K41) how can I get the [h]:mm cell formatting to work, anyone know? Thanks once again in advance. |
#4
|
|||
|
|||
Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
On Sun, 16 Oct 2005 11:02:00 -0700, "Peo Sjoblom"
wrote: Wow! No need for that, if the values are time values you can just use =ROUND(SUM(K16:K41)*96,0)/96 Looks like. I don't know where I get these things <g. The above seems to work just fine, too, so I switched. And now the cell formatting change to [h]:mm (which for me and this spreadsheet ends up being [h]"h"mm"m") works just superbly so far! I have another 2 months of overtime to do in another sheet so that will be the test. Thanks everyone!!! :oD You're all life-savers once again. to round to the nearest quarter btw, time can only handle up to 24 hours -- Regards, Peo Sjoblom (No private emails please) "StargateFan" wrote in message .. . A spreadsheeI that allows me to enter hours is not adding up correctly in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of doing what it's doing? oh, btw, the code is this for the totals cell: =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K41) )/15,0)*15,0) Thanks! |
#5
|
|||
|
|||
Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
On Sun, 16 Oct 2005 14:05:35 -0400, StargateFan
wrote: On Sun, 16 Oct 2005 13:39:23 -0400, StargateFan wrote: A spreadsheeI that allows me to enter hours is not adding up correctly in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of doing what it's doing? oh, btw, the code is this for the totals cell: =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K4 1))/15,0)*15,0) Thanks! The [h]:mm cell formatting so that totals don't go over 24 hours works but only when not rounding down. With the rounding down code of =TIME(HOUR(SUM(K16:K41)),ROUND(MINUTE(SUM(K16:K41 ))/15,0)*15,0) rather than just =SUM(K16:K41) how can I get the [h]:mm cell formatting to work, anyone know? Thanks once again in advance. Sorry, another response just came in. Changing the first round down code to this simpler one: =ROUND(SUM(K16:K41)*96,0)/96 fixed the problem, it seems. The new cell formatting now is obeyed and I'm getting 25h30m. So the <24 hours is showing as well as rounding down to 30m from 31m! Excellent. Thanks! :oD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I list items in a column with totals? | Excel Discussion (Misc queries) | |||
How do I subtract 20% from one column in Excel and place totals i. | Excel Discussion (Misc queries) | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions | |||
How do I multiply Column G Totals by a number and display the res. | Excel Discussion (Misc queries) | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |