Hello,

I am trying to add minutes up in Excel, but I can't figure out which formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value (1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and keep
the value an integer? Any help would be greatly appriciated. Thanks.

As long as you just use minutes like in your example this will work

=--TEXT(TEXT(INT(SUM(A1:A2)/60)&MOD(SUM(A1:A2),60),"00\:00"),"h.m")


--

Regards,

Peo Sjoblom

"Gabe" wrote in message
...
Hello,

I am trying to add minutes up in Excel, but I can't figure out which
formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value (1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and keep
the value an integer? Any help would be greatly appriciated. Thanks.

On Mon, 19 Dec 2005 15:06:02 -0800, "Gabe"
wrote:

Hello,

I am trying to add minutes up in Excel, but I can't figure out which formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value (1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and keep
the value an integer? Any help would be greatly appriciated. Thanks.


=INT((A1+A2)/60)+MOD(A1+A2,60)/100

Format the result as number with two decimal places.


--ron

Forget that one, won't work if total is less than a certain number

=--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00"))


--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
As long as you just use minutes like in your example this will work

=--TEXT(TEXT(INT(SUM(A1:A2)/60)&MOD(SUM(A1:A2),60),"00\:00"),"h.m")


--

Regards,

Peo Sjoblom

"Gabe" wrote in message
...
Hello,

I am trying to add minutes up in Excel, but I can't figure out which
formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value
(1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and
keep
the value an integer? Any help would be greatly appriciated. Thanks.

"Gabe" wrote in message

Hello,

I am trying to add minutes up in Excel, but I can't figure out which
formula to use to display the total correctly? I am trying to add
55 plus 55 as integers(110), I need the total to be displayed as
1.50 (.50) being the difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value
(1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333)
and keep the value an integer? Any help would be greatly
appriciated.
Thanks.


Hi Gabe,

I think you are mixing units which is causing the problem.

If you have A1 and A2 as above (I am assuming that the values in each
of those cells is actually 55 rather than '55 mins' which has a value
of 0.03819444...) then you can enter the following formula in A3:

=SUM(A1:A2)/(24*60)

Which evaluates to 0.0763888... which is 1 hour, 50 mins (the standard
unit of time in excel is a day = 24 hours).

To make it display as you set out above, change the number format for
that cell to:

h.mm

I would suggest you don't use that format though. It is ambiguous as
to whether you mean 1 hr, 50 mins or 1.50 hours (which is equal to
1hr, 30 mins).

Personally I would use this format:

hh:mm:ss

The use of a colon is generally accepted to indicate a separation
between hours, mins, and seconds so at least people will know it is a
time value of 1 hr, 50 mins (rather than 1 min, 50 secs).

Obviously you choice though!

HTH,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

That worked great! Your'e a genius! Thank you so much for teaching me that, I
really appriciate it.

Gabe

"Ron Rosenfeld" wrote:

On Mon, 19 Dec 2005 15:06:02 -0800, "Gabe"
wrote:

Hello,

I am trying to add minutes up in Excel, but I can't figure out which formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value (1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and keep
the value an integer? Any help would be greatly appriciated. Thanks.


=INT((A1+A2)/60)+MOD(A1+A2,60)/100

Format the result as number with two decimal places.


--ron

Ron,

In the formula you provided, how can I altar it to add a range instead of
only two cells?

Instead of, A1+A2,
Be, the SUM of A1:A20

"Ron Rosenfeld" wrote:

On Mon, 19 Dec 2005 15:06:02 -0800, "Gabe"
wrote:

Hello,

I am trying to add minutes up in Excel, but I can't figure out which formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value (1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and keep
the value an integer? Any help would be greatly appriciated. Thanks.


=INT((A1+A2)/60)+MOD(A1+A2,60)/100

Format the result as number with two decimal places.


--ron

Actually I need the time to be in a certain integer form so that it can be
multiplied by a set price per minute. Sorry I forgot to clarify that from
the begining. Thanks for the help though.

Gabe

"Alan" wrote:

"Gabe" wrote in message

Hello,

I am trying to add minutes up in Excel, but I can't figure out which
formula to use to display the total correctly? I am trying to add
55 plus 55 as integers(110), I need the total to be displayed as
1.50 (.50) being the difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value
(1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333)
and keep the value an integer? Any help would be greatly
appriciated.
Thanks.


Hi Gabe,

I think you are mixing units which is causing the problem.

If you have A1 and A2 as above (I am assuming that the values in each
of those cells is actually 55 rather than '55 mins' which has a value
of 0.03819444...) then you can enter the following formula in A3:

=SUM(A1:A2)/(24*60)

Which evaluates to 0.0763888... which is 1 hour, 50 mins (the standard
unit of time in excel is a day = 24 hours).

To make it display as you set out above, change the number format for
that cell to:

h.mm

I would suggest you don't use that format though. It is ambiguous as
to whether you mean 1 hr, 50 mins or 1.50 hours (which is equal to
1hr, 30 mins).

Personally I would use this format:

hh:mm:ss

The use of a colon is generally accepted to indicate a separation
between hours, mins, and seconds so at least people will know it is a
time value of 1 hr, 50 mins (rather than 1 min, 50 secs).

Obviously you choice though!

HTH,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

Alright this is exactely what I was looking for! Thanks Peo.

Gabe

"Peo Sjoblom" wrote:

Forget that one, won't work if total is less than a certain number

=--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00"))


--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
As long as you just use minutes like in your example this will work

=--TEXT(TEXT(INT(SUM(A1:A2)/60)&MOD(SUM(A1:A2),60),"00\:00"),"h.m")


--

Regards,

Peo Sjoblom

"Gabe" wrote in message
...
Hello,

I am trying to add minutes up in Excel, but I can't figure out which
formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value
(1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and
keep
the value an integer? Any help would be greatly appriciated. Thanks.

"Gabe" wrote in message

Actually I need the time to be in a certain integer form so that it
can be multiplied by a set price per minute. Sorry I forgot to
clarify that from the begining. Thanks for the help though.

Gabe


No problem, just multiply the result by (24*60 = 1440) to get to units
of a minute.

HTH,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

On Mon, 19 Dec 2005 16:02:02 -0800, "Gabe"
wrote:

Ron,

In the formula you provided, how can I altar it to add a range instead of
only two cells?

Instead of, A1+A2,
Be, the SUM of A1:A20

=INT(SUM(A1:A20)/60)+MOD(SUM(A1:A20),60)/100



--ron

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?

"Peo Sjoblom" wrote:

Forget that one, won't work if total is less than a certain number

=--(INT(SUM(A1:A2)/60)&"."&TEXT(MOD(SUM(A1:A2),60),"00"))


--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
As long as you just use minutes like in your example this will work

=--TEXT(TEXT(INT(SUM(A1:A2)/60)&MOD(SUM(A1:A2),60),"00\:00"),"h.m")


--

Regards,

Peo Sjoblom

"Gabe" wrote in message
...
Hello,

I am trying to add minutes up in Excel, but I can't figure out which
formula
to use to display the total correctly? I am trying to add 55 plus 55 as
integers(110), I need the total to be displayed as 1.50 (.50) being the
difference in minutes. Here is a better look.

A1 = 55, A2 = 55, A3 =SUM(A1:A2)/60, A3 displays the value
(1.8333).

Is there anyway to display the value as 1.50, instead of (1.8333) and
keep
the value an integer? Any help would be greatly appriciated. Thanks.