Number formatting help

[bvbaby]

Hi I have two columns of cells that need to be reformatted: B and C.

In the B column excel is reading the cells as time (0:41:55.0), I need it to display those cells as a number like this: 00 41 55.0.

In column C the cells look like this: 41 : 09 : 47.3
I need the C column cells to look just like the formatted B column cells (41 09 47.3)

Also, even though they have the spaces, the numbers should be read by excel as one whole number (i.e. 41 09 47.3 would be used in a calculation as 410947.3)

Thanks

[stanleydgromjr]

bvbaby,

Try Custom:
hh mm ss.0

[oldchippy]

Hi I have two columns of cells that need to be reformatted: B and C.

In the B column excel is reading the cells as time (0:41:55.0), I need it to display those cells as a number like this: 00 41 55.0.

In column C the cells look like this: 41 : 09 : 47.3
I need the C column cells to look just like the formatted B column cells (41 09 47.3)

Also, even though they have the spaces, the numbers should be read by excel as one whole number (i.e. 41 09 47.3 would be used in a calculation as 410947.3)

Thanks

Hi,

For column B with (0:41:55.0) in another column

=TIMEVALUE(MID(B1,2,9)) custom format the cell [h] mm ss.0

For column C with 41 : 09 : 47.3 custom format the cell to ([h] mm ss.0)

[bvbaby]

Solved:

I ended using oldchippy's advice for column b and in column c I used Custom -> "00 00 00.0"