Results 1 to 7 of 7
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Serial number to date (2003 SP3)

    I have a column with numers such as 1185303588000000. This field was imported from a text file, in which the field was formatted as mm/ss/yyyy hh:mm:ss. I can't seem to find a function to convert the number to a date. Thanks.
    JimmyW
    Helena, MT

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

    Re: Serial number to date (2003 SP3)

    Could you attach a small sample text file?

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Serial number to date (2003 SP3)

    Here's a sample from the larger file. Thanks!
    Attached Files Attached Files
    JimmyW
    Helena, MT

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

    Re: Serial number to date (2003 SP3)

    Do you have any idea what dates/times those numbers are supposed to represent?

  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Serial number to date (2003 SP3)

    Extremely wide picture cropped by HansV to show only the relevant part

    There are two date fields. One, "Last Visited," may be populated. The second, "Last Load Time" is always populated. I attached a screen shot from the program that produced the text file.
    Attached Images Attached Images
    • File Type: jpg x.jpg (13.8 KB, 0 views)
    JimmyW
    Helena, MT

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

    Re: Serial number to date (2003 SP3)

    Thanks. The numbers in your text file are UNIX time in microseconds.
    With one such number in cell E3, the formula

    =E3/86400000000+DATE(1970,1,1)

    will return the Windows date/time. You'll have to format the cell with the formula as a date or date+time.

    See the attached workbook.
    Attached Files Attached Files

  7. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Serial number to date (2003 SP3)

    Ah ha! I should have recognized the format, but I still wouldn't have figruied out the formula! Once again, Hans, you have come to my rescue. I appreciate your time and talents very much.
    JimmyW
    Helena, MT

Posting Permissions

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