+ Reply to Thread
Results 1 to 6 of 6

How can I convert an exported UNIX timestamp in excel?

  1. #1
    prod sorter
    Guest

    How can I convert an exported UNIX timestamp in excel?

    When I export a table using phpMyAdmin I get a date string of:

    1064272434

    Which equates to:

    09/22/2003 07:13:54 PM

    Is there a formula for excel that would make this conversion for me,
    stripping the time of course?

    Result:

    09/22/2003


  2. #2
    Fredrik Wahlgren
    Guest

    Re: How can I convert an exported UNIX timestamp in excel?


    "prod sorter" <prodsorter@discussions.microsoft.com> wrote in message
    news:9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com...
    > When I export a table using phpMyAdmin I get a date string of:
    >
    > 1064272434
    >
    > Which equates to:
    >
    > 09/22/2003 07:13:54 PM
    >
    > Is there a formula for excel that would make this conversion for me,
    > stripping the time of course?
    >
    > Result:
    >
    > 09/22/2003
    >


    Snipped from http://www.sins.com.au/unix/unix-timestamp.html

    "This timestamp may be converted in EXCEL by using the following formula and
    choosing the option for the "1904 base": =(x+((365*66+17)*86400))/86400 If
    the "1900 base" is selected, then change the "66" value to "70". In Excel
    Date 0.41667 = 10 hours. What I ended up using:"
    =((B25+((365*70+19)*86400))/86400)-0.41667

    /Fredrik





  3. #3
    Fredrik Wahlgren
    Guest

    Re: How can I convert an exported UNIX timestamp in excel?


    "prod sorter" <prodsorter@discussions.microsoft.com> wrote in message
    news:9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com...
    > When I export a table using phpMyAdmin I get a date string of:
    >
    > 1064272434
    >
    > Which equates to:
    >
    > 09/22/2003 07:13:54 PM
    >
    > Is there a formula for excel that would make this conversion for me,
    > stripping the time of course?
    >
    > Result:
    >
    > 09/22/2003
    >


    Yeat another link:
    http://exceltips.vitalnews.com/Pages...me_Stamps.html

    /Fredrik



  4. #4
    Ron Rosenfeld
    Guest

    Re: How can I convert an exported UNIX timestamp in excel?

    On Sun, 20 Mar 2005 10:27:02 -0800, "prod sorter"
    <prodsorter@discussions.microsoft.com> wrote:

    >When I export a table using phpMyAdmin I get a date string of:
    >
    >1064272434
    >
    >Which equates to:
    >
    >09/22/2003 07:13:54 PM
    >
    >Is there a formula for excel that would make this conversion for me,
    >stripping the time of course?
    >
    >Result:
    >
    >09/22/2003


    A Unix time stamp is the number of seconds since 1/1/1970. And it is usually
    expressed as UTC.

    I suspect that your "equates to" is local time. Are you 4 hrs behind Zulu
    time?

    Because when I translate that date/time stamp I get 9/22/03 23:13:54


    In any event, to translate the UNIX timestamp into Windows PC time:

    =unix_time/86400 - DATE(1970,1,1)

    and to strip off the time:

    =INT(=unix_time/86400 - DATE(1970,1,1))

    However, you'd best get that time reference verified. The result does not make
    sense unless there is some time zone manipulation going on.


    --ron

  5. #5
    Dave Peterson
    Guest

    Re: How can I convert an exported UNIX timestamp in excel?

    And J.E. McGimpsey has both a worksheet formula and a UDF at:
    http://www.mcgimpsey.com/excel/udfs/unixtoxltime.html

    prod sorter wrote:
    >
    > When I export a table using phpMyAdmin I get a date string of:
    >
    > 1064272434
    >
    > Which equates to:
    >
    > 09/22/2003 07:13:54 PM
    >
    > Is there a formula for excel that would make this conversion for me,
    > stripping the time of course?
    >
    > Result:
    >
    > 09/22/2003


    --

    Dave Peterson

  6. #6
    JE McGimpsey
    Guest

    Re: How can I convert an exported UNIX timestamp in excel?

    here's a UDF that will do it:

    http://mcgimpsey.com/excel/udfs/unixtoxltime.html

    In article <9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com>,
    "prod sorter" <prodsorter@discussions.microsoft.com> wrote:

    > When I export a table using phpMyAdmin I get a date string of:
    >
    > 1064272434
    >
    > Which equates to:
    >
    > 09/22/2003 07:13:54 PM
    >
    > Is there a formula for excel that would make this conversion for me,
    > stripping the time of course?
    >
    > Result:
    >
    > 09/22/2003


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1