Hi guys,

I am trying to add up the time in one of my rows. How do I do that ??

For example I have the following times in my row E

E2 10.15
E3 1.25
E4 4.45
E5 2.15


The Total will now be 18 hrs 40 minutes ( adding manually)

But how I do that in Excel to get the right number of hours and
minutes. I did try to format the cells as Time. But the result is
something weird.

Any help ??


--
bengaluru
------------------------------------------------------------------------
bengaluru's Profile: http://www.excelforum.com/member.php...o&userid=24188
View this thread: http://www.excelforum.com/showthread...hreadid=384421

On Mon, 4 Jul 2005 20:00:33 -0500, bengaluru
wrote:


Hi guys,

I am trying to add up the time in one of my rows. How do I do that ??

For example I have the following times in my row E

E2 10.15
E3 1.25
E4 4.45
E5 2.15


The Total will now be 18 hrs 40 minutes ( adding manually)

But how I do that in Excel to get the right number of hours and
minutes. I did try to format the cells as Time. But the result is
something weird.

Any help ??

If your "." is the time separator in your locale, then you can merely SUM the
column.

If your "." is the decimal separator, and not the time separator, then try this
array-entered formula:

=SUM(--SUBSTITUTE(E2:E5,".",":"))

To array-enter a formula, after typing of pasting it into the cell, hold down
<ctrl<shift while hitting <enter. XL will place braces {...} around the
formula.


--ron

Hi Ron, great formula!

I got the value 0.777778 in the sum....just wanted the OP to know he might
need to reformat the result cell as time.

Bill


"Ron Rosenfeld" wrote in message
...
On Mon, 4 Jul 2005 20:00:33 -0500, bengaluru
wrote:


Hi guys,

I am trying to add up the time in one of my rows. How do I do that ??

For example I have the following times in my row E

E2 10.15
E3 1.25
E4 4.45
E5 2.15


The Total will now be 18 hrs 40 minutes ( adding manually)

But how I do that in Excel to get the right number of hours and
minutes. I did try to format the cells as Time. But the result is
something weird.

Any help ??

If your "." is the time separator in your locale, then you can merely SUM
the
column.

If your "." is the decimal separator, and not the time separator, then try
this
array-entered formula:

=SUM(--SUBSTITUTE(E2:E5,".",":"))

To array-enter a formula, after typing of pasting it into the cell, hold
down
<ctrl<shift while hitting <enter. XL will place braces {...} around
the
formula.


--ron

On Mon, 4 Jul 2005 22:12:48 -0400, "William Benson"
wrote:

Hi Ron, great formula!

I got the value 0.777778 in the sum....just wanted the OP to know he might
need to reformat the result cell as time.

Bill

Thanks for adding that.
\
And the format he should probably use would be [h]:mm in order to get hours
over 24.


--ron

Thanks Ron for further claifying the format. I was about to ask you ho
to get the result for over 24 hours.

Great man........Thanks

Take car

--
bengalur
-----------------------------------------------------------------------
bengaluru's Profile: http://www.excelforum.com/member.php...fo&userid=2418
View this thread: http://www.excelforum.com/showthread.php?threadid=38442

On Thu, 7 Jul 2005 07:07:12 -0500, bengaluru
wrote:


Thanks Ron for further claifying the format. I was about to ask you how
to get the result for over 24 hours.

Great man........Thanks

Take care

I'm glad it's working for you.

Thanks for the feedback.


--ron

Thanks a lot guys. Yes it does work.

Your help is much appreciated.


--
bengaluru
------------------------------------------------------------------------
bengaluru's Profile: http://www.excelforum.com/member.php...o&userid=24188
View this thread: http://www.excelforum.com/showthread...hreadid=384421