# How to change cell format to something that canbe calculated

1. ## 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...

2. ## Re: How to change cell format to something that canbe calculated

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

3. ## Re: How to change cell format to something that canbe calculated

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

4. ## Re: How to change cell format to something that canbe calculated

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

5. ## Re: How to change cell format to something that canbe calculated

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))

6. ## Re: How to change cell format to something that canbe calculated

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1