Adding Time (hours and minutes)

[bengaluru]

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

 

[Ron Rosenfeld]

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

 

[William Benson]

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]

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

 

[bengaluru]

Thanks a lot guys. Yes it does work.

Your help is much appreciated.

 

[bengaluru]

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

 

[Ron Rosenfeld]

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