Results 1 to 8 of 8
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Time is NOT a time (2003)

    The wife received a Excel file and some of the dates and time are NOT formated as such. We have tried to change them to the correct format but they will NOT really change.
    When we change the time format to "hh.mm.ss" the typed in "h:mm pm" will not change.
    See the attached sample. This is only about 2% of the file.
    Attached Files Attached Files

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time is NOT a time (2003)

    Hi Dave

    I just opened the attachment and the top entry was formated in time and then the 3rd entry on the list 13:30:55, I selected all of column B, format Cells and changed to Special hh:mm it does not automatically change the existing number but if you go into the cell and type 07 : 30 that is what it shows, I guess you could do a find and replace after that to change all of the same entries into the correct format.

    HTH

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Time is NOT a time (2003)

    This file needs to be correct format as it will be uploaded to a web site where this information will be machine converted using PHP scripts.

    Do you have any idea of how a file can become this *&^&**ed up?

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

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

    Re: Time is NOT a time (2003)

    A number of the values are text values with a non-breaking space after PM.
    Select column B.
    Then select Edit | Replace.
    Enter pm followed by Alt+0160 in the Find what box.
    Enter pm in the Replace with box.
    Click Replace All.

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Time is NOT a time (2003)

    Hans,
    That did the trick, the wife is real happy. She owes you BIG time.
    We also found the same "non-breaking space" in the Date column.

    Now what do we tell these people what NOT to do when making this file?

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time is NOT a time (2003)

    Is it possible that the data was copied from a web site and pasted into the XL worksheet? That is a good way to get unwanted non breaking spaces.
    Legare Coleman

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

    Re: Time is NOT a time (2003)

    Exactly what Legare mentioned. Tables on websites are often formatted with non-breaking spaces, and these cause problems when you import the data into Excel.

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Time is NOT a time (2003)

    That is what we are thinking and is most likely what happened.

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

Posting Permissions

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