Update Time Format in Cells
Results 1 to 7 of 7

Thread: Update Time Format in Cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Update Time Format in Cells

    I have a macro that runs to clean up a report however, the values in the report are time and formatted with [hh]:mm:ss. The problem I run into is Excel does not seem to format this correctly until you click into the formula bar and press enter. When this is completed, it formats the value correctly and then I can do the proper calculations.

    Is there a way to make this an automated function?

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Time Format in Cells

    Probably, but hard to say without seeing the code.

    Can you post the code?
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Time Format in Cells

    The macro is very simple, it only deletes a few columns and rows that are not needed. Nothing else fancy takes place. The report itself is an export from our Cisco call reporting system and as it exports, it would say something like the time spent in available status would be 00:30:42 (signifying someone was available for 30 minutes and 42 seconds).

    The issue is that the export is completely text, so when I format the cell to [hh]:mm:ss I have to click into the formula bar and press enter so that it automatically gives it the AM (it doesn't display that in the cell but it does in the formula bar)

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Time Format in Cells

    Without seeing your actual code, this is just a generalization of converting Text times to real times.
    Based on your statement that simply entering the formula bar and pressing enter sucessfully converts it.

    You can use

    Code:
    With Range("A1:A100")
        .Value = .Value
    End With

    The reason we would want to see your actual code is that there may be a way to adjust it.
    So that the times are entered correclty in the first place, rather than adding an additional step to 'convert' them afterwards.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Time Format in Cells

    I'll try that and see if that helps. The code in the macro is as follows.

    Code:
        Rows("1:6").Delete Shift:=xlUp
        Rows("2:6").Delete Shift:=xlUp
        Rows("3:7").Delete Shift:=xlUp
        Rows("4:8").Delete Shift:=xlUp
        Rows("5:9").Delete Shift:=xlUp
        Rows("6:10").Delete Shift:=xlUp
        Rows("7:11").Delete Shift:=xlUp
        Rows("8:12").Delete Shift:=xlUp
        Rows("9:13").Delete Shift:=xlUp
        Rows("10:24").Delete Shift:=xlUp
        Columns("B:D").Delete Shift:=xlToLeft
        Columns("D:E").Delete Shift:=xlToLeft
        Columns("E:F").Delete Shift:=xlToLeft
        Columns("F:G").Delete Shift:=xlToLeft
        Columns("G:G").Delete Shift:=xlToLeft
        Columns("H:H").Delete Shift:=xlToLeft

  6. #6
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Time Format in Cells

    Quote Originally Posted by Jonmo1 View Post
    Without seeing your actual code, this is just a generalization of converting Text times to real times.
    Based on your statement that simply entering the formula bar and pressing enter sucessfully converts it.

    You can use

    Code:
    With Range("A1:A100")
        .Value = .Value
    End With

    The reason we would want to see your actual code is that there may be a way to adjust it.
    So that the times are entered correctly in the first place, rather than adding an additional step to 'convert' them afterwards.
    Your code is working beautifully. Thanks again for all the help, I greatly appreciate it!

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Time Format in Cells

    You're welcome.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

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
  •