How do I get Excel 2002 to add times together?

L

lylehughey

I am trying to get it to add together using the TEXT function, and it will
only do it if i select and add each individual cell into the formula, is
there not an easier way to do this?
 
J

JulieD

Hi

could you type out a sample of your data, what you expect the answer to be
and the formula (in full) that you're currently using as i'm not sure how
you're adding things together using the text function.
 
L

lylehughey

The thing that i am trying to do is have it add, time that has been figured
by excel into a certain cell, the following formula is the only way that i
can get it to work. The cells are all in the same column, in this case "H"
this is the formula for adding the 4 cells that i got to work
=TEXT(H76+H77+H78+H79,"h:mm")
Though i tried doing it this way =TEXT(H76:H79,"h:mm") this does not work as
i get a totally different answer. The values in the cells are 0:53 , 1:26 ,
0:54 and 1:00 with the second formula i get a value of 1:00 with the first
formula i get the correct answer of 4:13. My question was, is there an
easier way to do this since this is an almost daily thing, and i have been
adding them by hand for about 6 months until i found this place. Thanks for
any help.

Lyle
 
C

Chip Pearson

Times are just numbers, between 0 and 1, indicating a portion of
a 24 hour day (0.25 = 6:00, 0.5 = 12:00, 0.75 = 18:00, etc), and
therefore you can simply add them together. E.g.,

=SUM(A1:A10)

Format the result cell with a custom number format of [hh]:mm or
[hh]:mm:ss


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...
 
L

lylehughey

Chip and JE,

Neither of those worked, Chip yours returned with a 00:00 and JE yours
returned 12:00:00AM, remember these are times that have been figured by excel
in a cell, the format that they are in is h:mm (4:10) as an elapsed time
already, i just need to get them added together.

Thanks

Lyle

Chip Pearson said:
Times are just numbers, between 0 and 1, indicating a portion of
a 24 hour day (0.25 = 6:00, 0.5 = 12:00, 0.75 = 18:00, etc), and
therefore you can simply add them together. E.g.,

=SUM(A1:A10)

Format the result cell with a custom number format of [hh]:mm or
[hh]:mm:ss


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
I am trying to get it to add together using the TEXT function,
and it will
only do it if i select and add each individual cell into the
formula, is
there not an easier way to do this?
Click to expand...
Click to expand...
 
J

JE McGimpsey

If they were really XL times figured by XL, the SUM() formula would have
worked.

How were they "figured"? If you used the TEXT function, then the values
are Text, not XL times, and SUM() will ignore them.
 
L

lylehughey

They are figured using TEXT, it is the only way that i could get it to figure
elapsed time, if you can point me in a different direction please do so. At
this point any help is greatly appreciated. Thanks again

Lyle
 
J

JE McGimpsey

Times in XL are just numbers - calculated as fractional days, so 6:00 =
0.25. I'm not sure how you're "figuring" elapsed times, but if you
format your cell the same way your TEXT() format string does, it should
work fine as numbers. To display elapsed times > 24 hours (XL by default
"rolls over" the display) use

Format/Cells/Number/Custom [h]:mm

See

http://cpearson.com/excel/datetime.htm#AddingTimes

for more.
 
Top