Day/Time Data to Something Useful?

[Clu]

Good morning everyone,

This is my first post here, and I'm hoping that someone here will understand this better than I do.

I am trying to use a number value in the following format: 000:00:43:48. This format is obviously ddd:hh:mm:ss. This format is very interesting as excel is allowing me to use it for virtually nothing.

For example, I am trying to multiply the number of minutes in the value by a sum of three other numbers.

So it would be =000:00:43:48*SUM(50+11+112).

This is turning up an #VALUE! when I run it in Excel.

Any ideas?

Clu

 

[Clu]

Ok... One of the guys in my office has figured this out. What we did was a

"=VALUE(LEFT(H2,3))" and "=TIMEVALUE(RIGHT(H2,8))" and then SUM the two together.

This yielded a workable number. Sorry for the confusion. I will have to work on articulating my descriptions.

Thanks folks!
Clu

 

[Yard]

Right, so it's not a number then.

Assuming that cell is A1 and your subscriber count is in B1, try this in C1:

=RIGHT(A9,2)/60+MID(A9,8,2)+MID(A9,5,2)*60+LEFT(A9,3)*60*24

which will give you the duration of the outage in decimal minutes. (I'm assuming the format of A1 will always be ddd:hh:mm:ss)

Then you can multiply B1 by C1 to get the total subscriber outage duration in decimal minutes.