Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I get Excel 2002 to add times together?
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? |
#2
|
|||
|
|||
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. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "lylehughey" wrote in 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? |
#3
|
|||
|
|||
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 "JulieD" wrote: 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. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "lylehughey" wrote in 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? |
#4
|
|||
|
|||
One way:
=SUM(H76:H79) Format the cell as a Time. In article , "lylehughey" wrote: 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. |
#5
|
|||
|
|||
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 "lylehughey" wrote in 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? |
#6
|
|||
|
|||
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" wrote: 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 "lylehughey" wrote in 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? |
#7
|
|||
|
|||
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. In article , "lylehughey" wrote: 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. |
#8
|
|||
|
|||
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 "JE McGimpsey" wrote: 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. In article , "lylehughey" wrote: 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. |
#9
|
|||
|
|||
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. In article , "lylehughey" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
Excel 2002 help | Excel Worksheet Functions | |||
Macro in Excel 2002 to save a workbook to a FTP location | Excel Discussion (Misc queries) | |||
Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) |