Time as a number

[NikSTHF]

Hi,

Trying to transform a time figure (hh:mm:ss) into just seconds (So 00:10:00 would be 600 seconds) and can't seem to get a formula to work at all.

I'm currently using the text to column function to split time down by the ":" - however, leaving the original format as "Time" makes the new columns fail upon running as they aren't time. When I switch them to string I can no longer perform a calculation on the columns as it gives me a "Can't do math on string columns" type error.

Any chance anyones got a work around for this?

I did try and use this tutorial, but I just find the same problems as above.

Bonus question: I've got about ten columns of this type of data, do I have to do each column as a different formula, or is there one big procedure I could use?

[NicoleJohnson]

You mentioned having to do his multiple times... You should also be able to use that formula on all 10 columns (as well as change the column type at the same time) using the Multi-Field Tool. You will just use the [_Current_Field_] instead of selecting individual fields in the formula, and select the option to change field type... will calculate them all at once! Easier than repeating the formula 10 times. :)

Cheers!
NJ

[mborriero · Accepted solution]

I have used a select tool to change the type from time to string. Then a text to column to split hour, minutes and seconds.

Another select tool to transform string in integer.

Finally, a formula that does the calculation that you want:

seconds + (minutes *60) + (hours *3600)

Hope it helps.

[NikSTHF]

Works perfectly like that, did the maths myself and it checks out.

Thanks!

[NikSTHF]

Hey NJ,

Wonder if you could elaborate a little on this?

I've just split all the columns into text and then sorted all the formatting out. But now I'm trying to get the Multi-formula to work with the following calc...

IF IsNull([Max_Status3]) THEN [Max_Status1]*60+[Max_Status2]
ELSE [Max_Status1]*3600+[Max_Status2]*60+[Max_Status3]
ENDIF

but for each column that the formula is needed. But I'm getting a parse error on this? I've selected the fields at the top so I know I'm asking it to do the right thing I just can't see the wood through the trees.

[mborriero]

@NikSTHFCould you please attach workflow or a screen shot of the error and the formula?

You said you are using a Multi-Formula, but I think you can just use a normal Formula.

[NikSTHF]

You can use the standard formula! Huzzah.

Thanks man.