Time Format of cell does not update until I double click on cell.
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Time Format of cell does not update until I double click on cell.

  1. #1
    New Member
    Join Date
    Dec 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Time Format of cell does not update until I double click on cell.

    I have a spreadsheet that is exported from AccuCall Web in Excel format. I need to sum the time of all long distance phone calls (which are all in a single column). When I use the Sum function and specify the range of cells with time, it shows 0:00:00 (I have formatted the cell containing the sum as [h]mm:ss). If I change the formatting of the individual cells in the column to 1:30:55 PM format, the cell still displays 0:01:22 for a one minute and 22 second call, and the sum does not change. However, if I double click on any of those formatted cells in the range and then simply hit escape to make no change, the sum properly adds the 0:01:22 (or whatever) to the total.

    Since the column contains hundreds of cells, I am looking for a way to not have to double click on each cell in order for the formatting of that cell to update. Suggestions?

    Scott
    Last edited by Macropod; Nov 14th, 2017 at 10:21 PM. Reason: Deleted employment/phone details for privacy reasons

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,961
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Have you got calculate turned OFF

    Tools > Options > Calculation "Automatic" ticked

  3. #3
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Welcome to the Board!

    Set the column to time and then select all the cells in that column. Go to Data-->Text To Columns and press finish. That should get them all in one swoop.

    Hope that helps.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  4. #4
    New Member
    Join Date
    Dec 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Automatic is ON. Should it be?

  5. #5
    New Member
    Join Date
    Dec 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    schielrn, that worked! Can't believe I wasted an hour on something you showed me how to fix in 3 seconds. Thanks!

    Scott

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,961
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Automatic should be on normally, just noticed, is the lack of a colon a typo here [h]mm:ss i.e does it actually look like [h]:mm:ss which would be right

  7. #7
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Quote Originally Posted by scott0728 View Post
    schielrn, that worked! Can't believe I wasted an hour on something you showed me how to fix in 3 seconds. Thanks!

    Scott
    I assume this data is either being pasted in from another program or imported in from somewhere else. This is a typical problem. Glad I could be of assistance, don't worry, you will learn from whatever you do in Excel and there are many things that I have worked on for days only to find there is something built into Excel already that took far less time.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  8. #8
    New Member
    Join Date
    Aug 2013
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Sorry, to bring up an old thread...
    I have the same problem, but in need the information to stay as data. If i do text to columns i cannot refresh data.

  9. #9
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    This is an issue I've had on several occasions, can't believe I just found this post 6 years after it was written. But I'm glad I did, it just saved me a lot of time, thanks!

    Quote Originally Posted by schielrn View Post
    Welcome to the Board!

    Set the column to time and then select all the cells in that column. Go to Data-->Text To Columns and press finish. That should get them all in one swoop.

    Hope that helps.

  10. #10
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Format of cell does not update until I double click on cell.

    Quote Originally Posted by jpasenko View Post
    Sorry, to bring up an old thread...
    I have the same problem, but in need the information to stay as data. If i do text to columns i cannot refresh data.
    Unfortunately I think if it is a refreshed data set, then you should be able to just format it using Control+1, but when you refresh the data it will probably go back to the "bad" format and you would ahev to re-format again. I have had that issue before as well.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com