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

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

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

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