Share via

How I convert EPOCH Time to Normal time and show it in Excel 2007?

Anonymous
2010-08-03T18:36:48+00:00

I have the formula but when I get the Excel time in Number Format and want to use a Time or Date format all I get is a bunch of ## characters and no matter how big I make the cell I do not get any result

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2010-08-03T22:30:55+00:00

    I wrote:

    fcoivan46 wrote:

    This is the formula I am using; =(((J2-(6*3600))/86400)+25569).

    [....]

     

    If you see the message "Negative dates or times are displayed as ####", your formula resulted in a negative value.

    Not necessarily.  Here is another way:

    =J2-6*3600/86400+25569

    Results in 1280878808.75 when J2 is 1280853240, the UTC for 3 Aug 2010 16:34 GMT.

    Results in "####" with the error "Negative dates or times are displayed as ####" when formatted as Custom "mm/dd/yyyy hh:mm" (without quotes).

    (Note that the error message is wrong!  It is really trying to say that the number, interpreted as a date/time serial number, exceeds the date limit of Excel -- about 2958465.99999999 in Excel 2003, exactly 12/31/9999 23:59:59.999.)

    Obviously my formula above is incorrect for the intended purpose.

    However, it is consistent with my theory that your formula is not really =(((J2-(6*3600))/86400)+25569), but something effectively closer to the fomula above due to some incorrect balancing of parentheses (or lack thereof).

    I have been suspicious of the correct entry of the formula because there are so many redundant parentheses, a recipe for human error.  That is why I suggested that you copy-and-paste the formula from Formula Bar into a response here.

    The correct succinct formula is =(J2-6*3600)/86400+25569.

    Copy-and-paste that formula into Excel to see if your problem goes away.

    10+ people found this answer helpful.
    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-08-23T12:04:55+00:00

    Hi again,

    I just figured it out.  The mistake was that the timestamp is in milli-seconds and I increased 3600 to 3600000 and 86400 to 86400000 and it worked.

    Sorry for the bother.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2013-07-31T01:25:07+00:00

    Reading through this thread helped, but I still got the ######## result.

    My Epoch time is in milliseconds as well and the formula which worked for me is:

    =((D2/1000)-6*3600000 )/86400000+25569

    where D2 was the Epoch time formulated as 1.36837E+15

    It needed to be 1.36837E+12 so I divided the Epoch time by 1000 and that produced the correct result.

    PHEW.

    3 people found this answer helpful.
    0 comments
  3. Anonymous
    2010-08-03T19:27:58+00:00

    This is the formula I am using; =(((J2-(6*3600))/86400)+25569). It returns a number but when i try to format the cell for a Time or Date the result come with the #### characters. I need to present the Date Format like 08/03/2010 10:34. If I am not wrong the DATEVALUE and TIMEVALUE functions are used when youneed to do the contrary. I appreciate any help!

    0 comments
  4. Anonymous
    2010-08-03T18:57:32+00:00

    What does your formula look like?

    Excel stores date and time as the integer and decimal part of a number. Time and Date formats expect that value.

    If your formula results in date and/or texts as strings, like "2010-08-03 20:55" you may have to use the functions DATEVALUE() and/or TIMEVALUE() to convert the string to the date/time value that Excel uses.

    Hope this helps / Lars-Åke

    0 comments