Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time field not recognized (Excel 2003 sp2)

    I use quite a few files that are downloaded from a mainframe to a local server. They are downloaded right to Excel as text, tab delimited. The problem I'm having is when a time shows as :31:04 with a colon as the first character. Excel then says the format is General, so unless I put a 0 at the beginnning, it isn't recognized as a time. Is there any way to search out and change all occurences like this? There's just too many to do individually. Going home now, so won't see any replies until Monday, but will sure make things easier if this can be done. Thanks in advance for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time field not recognized (Excel 2003 sp2)

    You could run this macro:


    Sub ChangeTime()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If oCell.HasFormula = False Then
    If Left(oCell, 1) = ":" Then
    oCell = "0" & oCell
    End If
    End If
    Next oCell
    End Sub

    If you want to apply the code to the current selection instead of to the entire used range of the active worksheet, change ActiveSheet.UsedRange to Selection.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    You've come through again! It's perfect. Thanks so much!

Posting Permissions

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