Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum of large minute/second figures
I'm trying to total a column with large minute values. For example, I want
to add 0:1125325:27 (1,125,325 minutes, 27 seconds) with 0:2358964:18 (2,358,964 minutes, 18 seconds. My total column shows as 00:00, no matter how I play with the format. I have the individual times formatted as h:mm:ss, and I have the total column formatted as [mm]:ss so the results will stay in minutes. However, it's almost like I need to have h:mmmmmmm:ss and [mmmmmmmm]:ss. However, that's not working for me. If I try to create h:mmmmmmm:ss, Excel saves it as h:mmmm:ss, and then it reads the 'm' as month instead of minute. I'm not getting anywhere. If anyone knows how to make this work, I would really appreciate it. Thank you! |
#2
|
|||
|
|||
The largest number that can be entered directly and interpreted as a
time is 9999:59:59 or 599999:59 in [m]:ss. The largest number of minutes entered in h:m:s format is similar: 0:9999:59. So your 0:112535:27 is likely not being parsed as a time, but as Text. Much larger values can be displayed. For instance, a million hours =1000000/24 will display as 60000000:00 when formatted as [m]:ss So if you want to add values that have the number of minutes =10,000, you'll have to use string manipulation: =MID(A1,3,FIND(":",A1,3)-3)/1440 formatted as [m]:ss gives 1125325:27. Do the same with your other values and you can use SUM() to sum them. In article , eacollins wrote: I'm trying to total a column with large minute values. For example, I want to add 0:1125325:27 (1,125,325 minutes, 27 seconds) with 0:2358964:18 (2,358,964 minutes, 18 seconds. My total column shows as 00:00, no matter how I play with the format. I have the individual times formatted as h:mm:ss, and I have the total column formatted as [mm]:ss so the results will stay in minutes. However, it's almost like I need to have h:mmmmmmm:ss and [mmmmmmmm]:ss. However, that's not working for me. If I try to create h:mmmmmmm:ss, Excel saves it as h:mmmm:ss, and then it reads the 'm' as month instead of minute. I'm not getting anywhere. If anyone knows how to make this work, I would really appreciate it. Thank you! |
#3
|
|||
|
|||
Excel is not seeing the values as h:mm:ss because there are too many digits
in mm. It is seen as text. The formula below will do your calculation, but the result will again be text. =VALUE(MID(A1,3,LEN(A1)-5))+VALUE(MID(A2,3,LEN(A1)-5))&":"&VALUE(RIGHT(A1,2))+VALUE(RIGHT(A2,2)) This assumes your original data is in A1 & A2 -- Ian -- "eacollins" wrote in message ... I'm trying to total a column with large minute values. For example, I want to add 0:1125325:27 (1,125,325 minutes, 27 seconds) with 0:2358964:18 (2,358,964 minutes, 18 seconds. My total column shows as 00:00, no matter how I play with the format. I have the individual times formatted as h:mm:ss, and I have the total column formatted as [mm]:ss so the results will stay in minutes. However, it's almost like I need to have h:mmmmmmm:ss and [mmmmmmmm]:ss. However, that's not working for me. If I try to create h:mmmmmmm:ss, Excel saves it as h:mmmm:ss, and then it reads the 'm' as month instead of minute. I'm not getting anywhere. If anyone knows how to make this work, I would really appreciate it. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to strip figures from cells text strings | Excel Discussion (Misc queries) | |||
create a top 10 list of sales figures | Excel Worksheet Functions | |||
Finding LARGE value within range of lookup table | Excel Discussion (Misc queries) | |||
Calculate minus figures only | Excel Discussion (Misc queries) | |||
Not enough memory for large workbooks | Excel Discussion (Misc queries) |