Need a formula to convert massive quantities of cells formatted in hh:mm:ss to seconds

[H3br3wHamm3r81]

Say I have a worksheet that has a column in which every cell is formated in hh:mm:ss format. The column is probably 100 cells long...so from A2 to A101 (A1 is the title of the column). Now, we need to take the data in cells A2:A101 and graph it, but the data needs to be formatted in only seconds. So, in column B, we want to convert the data from column A into seconds format.

For example,

cell A2: 00:01:46
cell B2: 106

Of course, we can't convert each cell by hand, because this needs to be done on a lot of data...worksheets upon worksheets. So, can anyone suggest a very efficient means to accomplish this?

[Richard Buttrey]

Hi,

Assuming A1 is a proper time number (.0012268518.....) and not a text string then in B1

Please Login or Register  to view this content.

formatted as a number.

[H3br3wHamm3r81]

Hi,

Assuming A1 is a proper time number (.0012268518.....) and not a text string then in B1

Please Login or Register  to view this content.

formatted as a number.

That's not a problem if we only need to convert one cell...it'd be a breeze.

But,

Of course, we can't convert each cell by hand, because this needs to be done on a lot of data...worksheets upon worksheets. So, can anyone suggest a very efficient means to accomplish this?

I need to convert at least 100 cells in column A (A2:A101) from hh:mm:ss format to seconds format in column B (B2:B101). And, this needs to be done on numerous worksheets.

[Richard Buttrey]

Hi,



I didn't mean you just to enter it in B1.

After entering it, copy it and paste it all down the column as you would do for any other multi copy tasks

And the if you want to automate it get a macro to perform the same task. i.e in a procesure which loops through alll sheets enter the formula in B1 of each sheet and then get the macro to copy and paste it to the last used row.

[H3br3wHamm3r81]

I bet you laughed your butt off there.

I don't use Excel everyday, but your advice to copy and paste that one cell down the column was what I needed to know. I knew there was a way to do that! I knew the conversion formula; I just forgot how to do the "multi copy."

Thanks Richard.

[Richard Buttrey]

I bet you laughed your butt off there.

No I wasn't laughing, but I must admit you had me wondering a bit, especially given the day you posted