Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Minutes in Excel cntd..
Peo,
This one worked, but now I am trying to add 26.25 plus 3.50, yet I come up with the answer of 29.75. I need it to read the same way so the value would be (30.15). Is there a way to modify the current formula so that these values add in multiples of 60 as well? A1 = 26.25, A2 = 3.50, A3 = A1+A2, A3 displays (29.75), can A3 display (30.15) instead? Original Formula you gave me: =--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00")) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Minutes in Excel cntd..
To interpret decimals as times instead of decimals, you will have to
work very hard. Why not enter 26:25 in one cell, 3:50 in another and add the two cells together. Excel will handle all the details as long as the cell format is [h]:mm or [h]:mm:ss (which Excel will defalt to if none of the 3 cells had formats applied before you started. Jerry Gabe wrote: Peo, This one worked, but now I am trying to add 26.25 plus 3.50, yet I come up with the answer of 29.75. I need it to read the same way so the value would be (30.15). Is there a way to modify the current formula so that these values add in multiples of 60 as well? A1 = 26.25, A2 = 3.50, A3 = A1+A2, A3 displays (29.75), can A3 display (30.15) instead? Original Formula you gave me: =--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00")) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Minutes in Excel cntd..
Actually, I am splitng the hours and minutes up from one cell (26.25) and
then multiplying each integer (26) & (25) by a certain price, to give me the price per hour and the price per minute. I would prefer not to use the text-to-column method to split the cell up (26.25). If I were to write it in a time format It would get all screwed up. But I can't figure out how I would go about adding 26.25 + 3.50 and getting the value to display the answer in multiples of 60, (30.15) instead of the standard (29.75)? Any Ideas? "Jerry W. Lewis" wrote: To interpret decimals as times instead of decimals, you will have to work very hard. Why not enter 26:25 in one cell, 3:50 in another and add the two cells together. Excel will handle all the details as long as the cell format is [h]:mm or [h]:mm:ss (which Excel will defalt to if none of the 3 cells had formats applied before you started. Jerry Gabe wrote: Peo, This one worked, but now I am trying to add 26.25 plus 3.50, yet I come up with the answer of 29.75. I need it to read the same way so the value would be (30.15). Is there a way to modify the current formula so that these values add in multiples of 60 as well? A1 = 26.25, A2 = 3.50, A3 = A1+A2, A3 displays (29.75), can A3 display (30.15) instead? Original Formula you gave me: =--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Minutes in Excel cntd..
Why would your calculation get messed up if you used a time format?
INT(time*24) gives the number of hours. MOD(time*24*60,60) give the number of minutes. If you are not willing to use Excel's built-in time capabilities, then you will have to work hard to sum times. For the sum, you can convert to time values (using a SUBSTITUTE function nested within a TIMEVALUE function, and then covert back with a TEXT function nested within a SUBSTITUTE function nested within a VALUE function; or you can split into hours and minutes, add them separately, and manually handle the carry at multiples of 60 instead of 100; or you can convert the minutes to factions of an hour, sum, and convert back. None of these approachs is very elegant. Jerry Gabe wrote: Actually, I am splitng the hours and minutes up from one cell (26.25) and then multiplying each integer (26) & (25) by a certain price, to give me the price per hour and the price per minute. I would prefer not to use the text-to-column method to split the cell up (26.25). If I were to write it in a time format It would get all screwed up. But I can't figure out how I would go about adding 26.25 + 3.50 and getting the value to display the answer in multiples of 60, (30.15) instead of the standard (29.75)? Any Ideas? "Jerry W. Lewis" wrote: To interpret decimals as times instead of decimals, you will have to work very hard. Why not enter 26:25 in one cell, 3:50 in another and add the two cells together. Excel will handle all the details as long as the cell format is [h]:mm or [h]:mm:ss (which Excel will defalt to if none of the 3 cells had formats applied before you started. Jerry Gabe wrote: Peo, This one worked, but now I am trying to add 26.25 plus 3.50, yet I come up with the answer of 29.75. I need it to read the same way so the value would be (30.15). Is there a way to modify the current formula so that these values add in multiples of 60 as well? A1 = 26.25, A2 = 3.50, A3 = A1+A2, A3 displays (29.75), can A3 display (30.15) instead? Original Formula you gave me: =--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
Whey is Excel 2003 calculating take so long - sometimes 20 minute | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) | |||
Convert seconds to minutes and seconds in excel | Excel Worksheet Functions |