12h02m23s (TEXT) -----> 12:02:23 (INTEGER)

Hello

I need to copy and paste downtime figures for work into Excel convert from Text to Integer so I can add up the totals

For example

12h02m23s has been copied from another report to excel I then need to add lots of these figures together so I assume it must be coverted to an integer in the format of hh:mm:ss?

Any help much appreciated!!

Thanks

Jonny

Assuming the example you gave is in A1:

Copy and paste this formula in B1.

Formula:

Please Login or Register  to view this content.

Format accordingly.

Thank you for the quick reply! I have tried this but it has left it still in text format can I somehow VALUE it?

-- should convert it into number.

Try:

Formula:

Please Login or Register  to view this content.

I worked it out I did a colum of

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D6,"h",""),"m",""),"s",""))

then next to it

=VALUE(TEXT(E5,"00\:00\:00"))

Thank you for the help!

Vikas Thank you that formula has cut out the extra column however it seems to change the larger numbers to something different?

Example

TOTAL 38h35m36s 14:35:36

Why is this?

Format as

Formula:

Please Login or Register  to view this content.

Perfect! Thank you so much

Jonny

Sorry zbor but figures are still as text if I use your formula I need vikas formula but able to deal with the high numbers

Thanks!

Jonny

Zbor with your formula this happens

39m32s 39:32:00

Any advice?

Thanks

Jonny

Vikas

Thank you again for the help

This is what I am getting when I open the sheet

38h35m36s 14:35:36
35m36s 00:35:36
38h36s 14:00:36
38h35m 14:35:00

I am hoping to get the numbers to be 38 not 14?

Thanks again

Jonny

Thats its Vikas!

Thank you so much helped me a great deal!

I just tried the formula with the full data and I think when it has just 1 figure for the hours instead of too it fails

38h35m36s 38:35:36
35m36s 00:35:36
38h36s 38:00:36
38h35m 38:35:00
1h23s23m 00:23:23

I am sorry I keep stealing your time Vikas but I very much appreciate it

Non Standardized data entries are the root of all evil in Excel.

As you've seen, it's extremely difficult to account for all the possible formats that the time can be entered.

Is it at all possible to standarize how the times are being entered?
At the very least, make it always include all 3 elements of hours minutes and seconds.
Even if you have to just type 0h or 0m or 0s

It is insane I have basic Excel knowledge but you guys are on another level!

I have managed to do it with 2 columns but can't get it down to 1 column

The manager wants it set up as easy as possible so we just copy and paste the report into a data sheet which then uses vlookups to pull all the data out for graphs and charts for the entire sawmill downtime daily analysis if the single digit hour figure can be cracked I think you guys have done it

Many thanks

Jonny

OK, so this is a sheet still in the works...
Not a set of data from somewhere else you're trying to convert.

Right?

For simplicity's sake, I suggest designating 3 comlumns.
1 for entering the hours - A1
1 for entering the minutes - B1
1 for entering the seconds - C1

Then use
=INT(A1/24)+TIME(A1,B1,C1)

Still custom format the cell as [hh]:mm:ss

My current set up is this for 2 columns

1st column:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"h",""),"m",""),"s",""))
This changes 02m23s to 223

2nd column:

=VALUE(TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"h",":"),"m",":"),"s","")),"00\:00\:00"))
This changes 223 to 0:02:23

and this works in all formats