Calculating Minutes in Excel

[Guest]

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.

 

[Peo Sjoblom]

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

 

[Ron Rosenfeld]

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

 

[Peo Sjoblom]

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

 

[Alan]

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:

(e-mail address removed)

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

 

[Guest]

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

Gabe

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

Gabe

 

[Alan]

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:

(e-mail address removed)

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

 

[Ron Rosenfeld]

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

 

[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?