Excel: Time calculations 1

[norty303]

How do you get Excel to sum a long list of times which are formatted as times? You can do a1+a2+a3 etc but you can't do a1:a3.

Obviouslyu this is a pain when you have long lists. Anyone got a solution?

Cheers

 

[xlbo]

erm
=SUM(A1:A3)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

erm.....


No!

As I said a1+a2+a3 works a1:a3 doesn't

 

[xlbo]

erm...Yes

Exactly how does it not work ??


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

OK, I've got a list of times which are formatted using the default time format in the options (hh:mm:ss)

if i add the formula =sum(a1+a2+a3) to the bottom cell (which is also formatted as time) then I get the correct results.

If I use the formula =sum(a1:a3) then I get 00:00:00 The total should only add up to a few minutes as I'm testing for a much larger data set.

 

[xlbo]

well let me guess - your "Dates" which you have formatted as such are actually text

try
=ISTEXT(Cell_Ref) on one of your "Dates", Let me know what it returns

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

OK, I've just run a test and if I create a new workbook and manually enter some data it DOES work correctly.

If I copy and paste some of the data into this workbook it doesn't!!??

What could be the problem with this data? It's formatted correctly.....

*boggled*

:-(

 

[norty303]

ok, you got it!!! They are text. *shamed* :-(

How do I go about converting them?

 

[norty303]

Mr xlbo, thanks very much, i've converted them to numbers now and it all works great.

You are truly a star...... and here's one for you!!

 

[xlbo]

Formatting does nothing to change a data TYPE - it can only affect the APPEARANCE of data.

To convert text to value, the quickest way is to enter a 1 into any blank cell
copy it
Select the data to convert
Edit>PasteSpecial - check Values AND tick MULTIPLY

Your times should convert to their proper serial numbers
Re-format to times et voila - you should be able to sum properly

As an addendum, the reason you can ADD but not SUM is that when you are adding, excel is forced to evaluate each cell. With the sum, it doesn't

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

oops - bit too late on the conversion help - glad you got it sorted

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

ps - if your sum of times is liable to go over 24 hours, you should apply the custom time format:
[hh]:mm:ss

this will allow the "Hours" to go above the logical limit of 24 - otherwise you will need to use d hh:mm:ss to get a sensible result

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

Yep, I'd already encountered the 24 hour limit when I searched for a solution. Thanks for being thorough!