+ Reply to Thread
Results 1 to 6 of 6

How to change cell format to something that canbe calculated

  1. #1
    Registered User
    Join Date
    11-22-2020
    Location
    CLE, USA
    MS-Off Ver
    10
    Posts
    3

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,040

    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. #3
    Registered User
    Join Date
    11-22-2020
    Location
    CLE, USA
    MS-Off Ver
    10
    Posts
    3

    Re: How to change cell format to something that canbe calculated

    Quote Originally Posted by Pete_UK View Post
    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
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,040

    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. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,730

    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. #6
    Registered User
    Join Date
    11-22-2020
    Location
    CLE, USA
    MS-Off Ver
    10
    Posts
    3

    Re: How to change cell format to something that canbe calculated

    Quote Originally Posted by Pete_UK View Post
    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
    Quote Originally Posted by bebo021999 View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. compare old value and change background colour of a DDE link and calculated cell
    By gpatel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-04-2015, 03:10 AM
  2. Replies: 3
    Last Post: 02-24-2015, 02:58 AM
  3. [SOLVED] Using a cell-linked text box to detect a calculated cell change and set value of scrollbar
    By darwin003 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2013, 08:29 PM
  4. [SOLVED] Do not display calculated cell value (Time format) in timesheet
    By kocco in forum Excel General
    Replies: 8
    Last Post: 04-11-2012, 09:02 AM
  5. how to change a calculated cell to a value
    By WarrenC in forum Excel General
    Replies: 11
    Last Post: 09-17-2008, 04:59 AM
  6. [SOLVED] how to change a calculated cell to = the calculated value
    By CAM in forum Excel General
    Replies: 4
    Last Post: 01-26-2006, 01:30 PM
  7. Change the color of a cell based on a calculated value
    By mkalavitz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2005, 02:05 PM

Bookmarks

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