How to change cell format to something that canbe calculated

I have some cells that are coming out in format ## hh:mm:ss (such as "01 10:07:09" for day 1 at 10hr 07min 09sec), but I cannot set cell D2-C2 to calculate the differences. What is the formating that I need to put in to do this?

I have tried a custom dd hh:mm:ss or d hh:mm:ss and not working...

I suspect your data is made up of text values which just seem to be in the format that you interpret them. Formatting will not affect text values.

It would help if you attached a sample Excel workbook - just follow the instructions in the yellow banner at the top of the screen.

Hope this helps.

Pete

Originally Posted by Pete_UK

I suspect your data is made up of text values which just seem to be in the format that you interpret them. Formatting will not affect text values.

It would help if you attached a sample Excel workbook - just follow the instructions in the yellow banner at the top of the screen.

Hope this helps.

Pete

Thank you Pete, please find attached file. I was working on the top row, so take caution there

You can use this formula in cell N2:

=IF(A2="","",LEFT(A2,2)+RIGHT(A2,8))

Apply a Custom Format to that cell of:

dd hh:mm:ss

then you can copy the formula into cells O2, P2 and Q2, and then copy those 4 formulae down to the bottom of your data. These will now be in numeric format and you will be able to perform arithmetic on them.

If you like, you could fix those values, and then copy/paste them back into the corresponding cells of columns A to D, and then you could delete columns N to Q.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can use this formula in cell N2:

=IF(A2="","",LEFT(A2,2)+RIGHT(A2,8))

Apply a Custom Format to that cell of:

dd hh:mm:ss

then you can copy the formula into cells O2, P2 and Q2, and then copy those 4 formulae down to the bottom of your data. These will now be in numeric format and you will be able to perform arithmetic on them.

If you like, you could fix those values, and then copy/paste them back into the corresponding cells of columns A to D, and then you could delete columns N to Q.

Hope this helps.

Pete

Originally Posted by bebo021999

to convert a cell (i.e, A2) under "dd hh:mm:ss" to date:
LEFT(A2,2)+RIGHT(A2,8)

B2-A2 should be:
=LEFT(B2,2)+RIGHT(B2,8)-(LEFT(A2,2)+RIGHT(A2,8))

Thank you! That helped a lot!