Calculating Minutes in Excel cntd..

[Gabe]

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

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

[Gabe]

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

[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

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