Calculating Minutes in Excel cntd..

[Guest]

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"))

 

[Jerry W. Lewis]

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

 

[Guest]

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?

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

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"))

 

[Jerry W. Lewis]

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

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?

:

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"))