Applying Custom Number Format
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Applying Custom Number Format

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've seen this issue on the board, but the answers so far aren't working for me.

    I have a spreadsheet which lists times in minutes and seconds measuring the time an agent is on a call with a customer. The file was output in excel format, but the time field is formatted as general.

    When I change the format to mm:ss AND RECALCULATE THE SHEET, the values don't change to the correct format. If I apply a SUM function, it reads as 00:00. Only by double-clicking each cell can I get the value to actually switch format and work in calculations.

    I MUST be doing something wrong, I hope. Can anyone advise me?

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-05 04:52, aaron_brown99 wrote:
    I've seen this issue on the board, but the answers so far aren't working for me.

    I have a spreadsheet which lists times in minutes and seconds measuring the time an agent is on a call with a customer. The file was output in excel format, but the time field is formatted as general.

    When I change the format to mm:ss AND RECALCULATE THE SHEET, the values don't change to the correct format. If I apply a SUM function, it reads as 00:00. Only by double-clicking each cell can I get the value to actually switch format and work in calculations.

    I MUST be doing something wrong, I hope. Can anyone advise me?
    Try CTRL+ALT+F9, this will force Excel to re-calc all cells.

    Does this work for you?

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried that as well; recalc seems to only force the reformat of the cells--it doesn't force the value to "take" into the cell, which I what I suppose double-clicking is doing.

    Any other ideas out there? Manually clicking in 60-120 cells at a time is a serious waste of time.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    This might not work either, but have you tried having another column just with the simple formula =A1 (or whatever volumn you have) and copying down, format that lot, copy, paste special values onto itself and then using that column instead?


  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you explain that again? I understand the simple formula part, but the rest was too fast.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    What I meant was, say your data is in cells A1 to A8, then in B1 you could have =A1.
    Then copy that formula down to cell B8 (simplest way just to hover with your cursor on the small black square on the bottom right corner of the cell, left click and drag it down).

    Then, select cells B1:B8, format them as you want.
    Then copy B1:B8,
    Click on Cell B1
    Edit>PasteSpecial.
    Click Values in the box that comes up.
    And that's it.

    I still really am not sure this is going to work though!

    (Feel free to email me the sheet, or at least an example, if you like though).



    [ This Message was edited by: AJ on 2002-04-05 06:40 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-05 06:25, aaron_brown99 wrote:
    Can you explain that again? I understand the simple formula part, but the rest was too fast.
    Aaron, the problem is that your times are really text values, and text values aren't affected by a time format. Furthermore, recalculating a worksheet has no effect on cell formatting. Here's the simple fix... Select your "time" values, choose the Data | Text to Columns... menu command, and press [ Finish ].

    That's it!

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Beautiful!! That works well, I appreciate it. The macro recorder captured that action and I've incorporated it into my VBA sub so it's done for me!

    This board is incredible, thank you all!

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