Results 1 to 10 of 10

Thread: Unix time stamp

  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Exclamation Unanswered: Unix time stamp

    Unix time stamp (a method of the showing date) is a umber of seconds from 1st of january 1970 to the present time.
    How can it be done in Excel?
    I mean in which way it should be converted from the figure (time stamp) to the date:hour format?
    Any formula or something like that?
    Thx for any help

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    #01-01-1970# + UnixTimeStamp / 60 / 60 / 24

    / 60 => To minutes
    / 60 => To hours
    / 24 => Days

    that should work....

    Regards

  3. #3
    Join Date
    Apr 2004
    Posts
    8
    Great thanx for the answer namliam

    I think the problem is a bit deeper as every 4 years, the year is longer-366 days instead of 365
    So a formula should be more complicated unfortunately.
    Anyway-thanx again for your time.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    No it should NOT! try it you will find it works ... this is because a date actually is a double, thus if unix counts seconds for a leap year it will count 60*60*24*366 seconds, where Excel will count just the 366 days.

    Regards

  5. #5
    Join Date
    Apr 2004
    Posts
    8
    Wow-great thanx
    One big reuest-can you say what exactly it needs to be put in the cell (how above formula gotta be written)?
    I'm sorry-I am not too good in Excell
    For example-let's say, my UnixTimeStamp value is in the cell B1
    it should be
    =B1 / 60 / 60 / 24 + what?

    You are great-thx again for your time

  6. #6
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Try...

    Try...

    =B1 /60/60/24 + DATE(1970,1,1)

    I think this should work regardless if your system is using the 1900 Date System (Excel Default set-up) or the optional 1904 Date System.

    -Mike

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I KNOW it works... on every date system, tho you may have to change 1-1-1970 to 31-12-1969 depending on the exact setup of the timestamp...

  8. #8
    Join Date
    Apr 2004
    Posts
    8
    It works like a charm.
    Great thanx for your help guys.
    It covers my axx at work
    Thx again

  9. #9
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Credit to namliam

    Props to namliam for his knowledge. I was just around to add a DATE formula.

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Now if only someone would help me , but my problems seem to stump everyone...

    Regards

Posting Permissions

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