Can't SUM 00:00:00:00 - How to convert or keep in the same column? & Cell Info Extraction

[MojoMonkey7]

Hi all,

Now I've scoured the forums for this and have tried all the formulas proposed and they just don't work for what I'm trying to do. I've tried text to columns as well and that doesn't work. Now I want to SUM these times - I appreciate they're not in a number format but a text format. But how do I get it to recognise it? This is just a few of the numbers - there are over 300 in a sheet normally I need to sum. I then want to return this total into a new cell. I've also tried converting the cell and removing the first two digits but then there's no number in the cell to SUM.

00:00:00:33
00:00:00:35
00:00:12:06
00:00:26:40
00:00:00:00
00:00:00:00
00:00:00:33
00:00:34:09
00:00:24:24

There is also another issue I'm encountering. I would like to extract three digits from a different cell and, when those digits exist. Rename a different cell with those digits. For example: A1 is my empty column. I would like A1 to be named from the first 3 digits of B2 based on a formula. I had a look at IF formulas but am having trouble.

Thanks for your help.

[XLent]

Re: first query - it's not clear whether the 33 in first example is meant to reflect seconds, or milliseconds?

Seconds:

=SUMPRODUCT(LEFT(A1:A9,2)+RIGHT(A1:A9,8))
format as [hh]:mm:ss

Milliseconds:

=SUMPRODUCT(SUBSTITUTE(A1:A9,":",".",3)+0)
format as [hh]:mm:ss.000

[Richard Buttrey]

Welcome to the forum
Re #1

I'm assuming the last 8 characters are hh:mm:ss
Use a helper column to convert these to a proper time number with

Please Login or Register  to view this content.

and copy it down. Then sum the column with =sum(B:B) custom formatted as "[hh]:mm:ss"

Re #2

If you want to name a cell automatically rather than manually then you'll need VBA with something like

Please Login or Register  to view this content.

[MojoMonkey7]

Hi,

Sorry 00:00:00:00 is DD:HH:MM:SS

[MojoMonkey7]

Woo! Yes it's seconds DD:HH:MM:SS. This works perfectly, thank-you!

[MojoMonkey7]

Hello,

Sorry I've come back to this if you don't mind.

I've hit a block as I now have numbers that are 04:02:41:38 ; 01:18:46:29 and when I use the formula =SUMPRODUCT(SUBSTITUTE(G54:G55,":",".",3)+2) it's dropping the last two digits and I'm getting 05:21:28. Very new to this so not quite sure what's involved in the formula.

If you could help that would be great!

Thanks

[Richard Buttrey]

What's the sum you require?

Hours
Days and decimals of a day

Some specific results would be useful.

[MojoMonkey7]

04:02:41:38
01:18:46:29

I'd like the sum of these two numbers for example. So it would be DD HH MM SS. The formula that I showed below works but I need the last two digits. At the moment it's giving me 05:21:28 but I'd like the extras as well so 38 secs and 29 secs. so my output would be 00:00:00:00

[Richard Buttrey]

So what's the actual result given those two numbers?

Is it

05:21:28:07

[XLent]

You should be using the first SUMPRODUCT provided in post#2 not the second -- as the first treats the last two digits as seconds as opposed milliseconds.

if the above values were in A1:A2 then:

=SUMPRODUCT(LEFT(A1:A2,2)+RIGHT(A1:A2,8))

format as dd:hh:mm:ss would return 05:21:28:07

[MojoMonkey7]

Thanks again! It was the formatting I was doing wrong. Are you able to explain how the 2 and 8 work? Is it to do with the number digits?

[XLent]

Yes, correct.

Apologies in advance if this is already know to you but in XL 24 hours equates to 1 -- so, for ex.:

if you entered 2.5 in a cell and format as dd:hh:mm:ss you would see 02:12:00:00 (2.5 days)

We can illustrate how the formula works by entering the same value, 02:12:00:00, directly into a cell

we know this format is not accepted by XL as a real datetime value and thus it cannot be aggregated etc

the formula is basically trying to convert the string to it's numeric equivalent (2.5) such that it can be aggregated

it basically takes the string, and splits the 'days' (first 2 characters) from the 'time' (8 rightmost characters)

the subsequent addition of these two elements 'coerces' the strings to their numeric equivalent - so "02" becomes 2, and "12:00:00" becomes 0.5 -- result thus being 2.5

now that the value is numeric it follows you can aggregate with other similar values, and then format the result.

one final point worth mentioning - the dd:hh:mm:ss format will only allow you to handle values up to 31 days and 23:59:59... thereafter you would have to format as [hh]:mm:ss to see true cumulative (this is because dd represents day of month, and on a 1900 date system, the integer value of 32 equates to date serial of 1st Feb 1900 so you would get 01 for dd).