Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Converting normal time to Epoch time format using excel

This is a discussion on Converting normal time to Epoch time format using excel within the Excel Questions forums, part of the Question Forums category; I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix ...

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Location
    Houston, Tx
    Posts
    54

    Default Converting normal time to Epoch time format using excel

    I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.

    Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?

    I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.

    Thanks

    Josh

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Converting normal time to Epoch time format using excel

    Hello,

    I think I'm correct in saying that UNIX time is the number of seconds that has elapsed since 1/1/1970. Excel calculates dates by using the number of days that has elapsed since 1/1/1900. Therefore you should be able to convert from one to the other by converting from seconds to days, and then adding on the 70 odd years difference.

    The formula :- =(A1/86400)+25569 where A1 contains the UNIX time should convert to Excel date/time. Make sure you format the cell as the required date/time format.

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    Houston, Tx
    Posts
    54

    Default Re: Converting normal time to Epoch time format using excel

    That does seem to work to go from epoch to normal time, but what I really need is to go from normal time to epoch time.

    Like you said, since the excel time function goes from 1/1/1900 I assume you'd be able to rig something......

  4. #4
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Converting normal time to Epoch time format using excel

    Just do the opposite mate:-

    =(A1-25569)*86400

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    Houston, Tx
    Posts
    54

    Default Re: Converting normal time to Epoch time format using excel

    That seems to work

    Thanks

    Josh

  6. #6
    New Member
    Join Date
    Oct 2006
    Posts
    12

    Default Re: Converting normal time to Epoch time format using excel

    How can I use this formula (=(A1/86400)+25569) but account for my time zone?
    IE: I enter decimal time of 1213636585 and the formula gives me "6/16/2008 17:16:25" which is GMT, but my server is in EST, so I want it to read 6/16/2008 13:16:25".

    I also need to account for all four US time zones.

    Thanks
    Brian

  7. #7
    New Member
    Join Date
    Jul 2009
    Posts
    2

    Default Re: Converting normal time to Epoch time format using excel

    Hi!

    I found this site that had info on accounting for time zones:

    http://untangible.com/blog/2009/01/c...-examples.html

    DVENUS

  8. #8
    New Member
    Join Date
    Mar 2010
    Posts
    4

    Default Re: Converting normal time to Epoch time format using excel

    I'm attempting the same epoch conversion in Excel 2007 but the result is nothng but tons of # signs in the cell. For the cell format I've tried mm/dd/yyyy hh:mm along with just about all others and the same result happens. What am I doing wrong here?

  9. #9
    New Member
    Join Date
    Jul 2009
    Posts
    2

    Default Re: Converting normal time to Epoch time format using excel

    Hi!

    Not sure this is your problem but make sure your Excel column is wide enough. You can either double click on the column seperator or drag it.

    Dave Venus

  10. #10
    New Member
    Join Date
    Mar 2010
    Posts
    4

    Default Re: Converting normal time to Epoch time format using excel

    Thanks. I've tried adjusting the column width which makes no difference. I can make it as wide as I want and still filled with all ## symbols.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com