Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date conversion issue (Excel 97)

    I have file (45,534) records and it was sent as a txt file. The user sent it to me in an excel format. They can not sort correctly by date. The date field looks like a date but it really is not. I know you can press F2 to "reformat" the field and when I do this it sorts correctly - in date order. I have a lot of records, is there a way to do the F2 function for all records at the same time? Currently, I am clicking on each one and press F2 and then enter.

    Thanks,

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

    Re: Date conversion issue (Excel 97)

    Try the following:
    - Select an empty cell.
    - Copy it to the clipboard (for example by pressing Ctrl+C).
    - Select the column or columns with date values that are not dates.
    - Select Edit | Paste Special...
    - Click the Add option, then click OK.
    - Format the selected column(s) as a date again.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date conversion issue (Excel 97)

    It worked great. Explain to me why you used the ADD option.

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

    Re: Date conversion issue (Excel 97)

    Dates are stored in Excel as numbers (the number of days since December 31, 1899). In your file, they were text values. The Paste Special operation forces Excel to interpret them as number values by adding an empty cell, i.e. 0 to each value. The formatting then displays the values as dates again.

    (You could also have selected a cell containing the number 1, copied it, then Paste Special with the Multiply option. It would have had the same result)

Posting Permissions

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