Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date/Time format (O2003 WXpHeSp3)

    From a broadband speed tester I can export the results to an xml file, import it to xl but then cannot format the cell to give me a meaningful result. Here it is 1214826171 Any translation help will be appreciated.
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: Date/Time format (O2003 WXpHeSp3)

    The number is probably interpreted as text. Make sure that there are no spaces before or after the number in the cell. If you press Enter after editing the cell, can the number be formatted?

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

    Re: Date/Time format (O2003 WXpHeSp3)

    BTW what does the number represent? Bits per second, bytes per second, ...? I don't see what a date/time format has to do with it.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time format (O2003 WXpHeSp3)

    Hi Hans,
    There is an xml export from the tesater which i then import to xl. This number does represent date and time of test - this is how it shows up after import.
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time format (O2003 WXpHeSp3)

    Hi again Hans,
    there are no spaces either before or after the numbers in the cells. Numbering suggests a universal date month hours minutes; but all of the formats show up as ********************
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time format (O2003 WXpHeSp3)

    I think the 1214826171 is the number of seconds since 1/1/1970.

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

    Re: Date/Time format (O2003 WXpHeSp3)

    Yes, it's probably UNIX time.

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

    Re: Date/Time format (O2003 WXpHeSp3)

    As mbarron notes, it's probably the number of seconds since midnight on January 1, 1970. This is the way UNIX stores dates/times.
    With the value in A1, the following formula will convert it to Windows time:

    =A1/86400+DATE(1970,1,1)

    You can format this as a date or date+time. If you want to take British Summer Time into account, add 1 hour:

    =A1/86400+DATE(1970,1,1)+1/24

Posting Permissions

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