converting to Day, Date, Time from a 13 digit integer

[nitebyfly]

I have a data set that has, for example, 1288865380025 as the cell readout, but is coded as 1.28886E+12. How do you convert this number to Day, Date, Time?

Thanks

 

[konew1]

what date and time does that number represent

 

[Ruddles]

10:09:40am on 4th November 2010?

If so, try:-

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

and format the call as a Date and/or a Time.

 

[nitebyfly]

Thank you. You got the date and time correct! (save the time is 4 hours earlier) - I, on the other hand have not yet got it to go. Say A1 looks like
<table x:str="" style="border-collapse: collapse; width: 65pt;" border="0" cellpadding="0" cellspacing="0" width="87"><col style="width: 65pt;" width="87"><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 65pt;" x:num="1288846934591" align="right" height="17" width="87">1.28885E+12</td> </tr></table>and the number displayed is 1288846934591 I tried your formula (thank you again) and the calculation cell displays ############. which it shows "negative dates or times are displayed as #####" The cell is formatted to a day, date format. Suggestions? If I finally get this, how do I correct for the 4 hour difference?

Thanks

 

[barry houdini]

I think you need a few more zeroes on Ruddles suggested 86400, try this formula

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

format as date and time - you might have to make a time adjustment too because that's based on GMT

 

[nitebyfly]

Thank you - Are you guys GREAT or what!! Yes, I have it going - Still don't know how to correct for the time difference. Thank you again.

Tim

 

[barry houdini]

To adjust to 4 hours earlier you can use this version

=A1/86400000+DATE(1970,1,1)-"4:00"

 

[nitebyfly]

Got it - three adjacent columns - first (A1) has data; B1 - format as Date (ex. Thursday, November 4, 2010) and C1 as time (1:30:55 PM)

Data in A1; =A1/86400000+DATE(1970,1,1) in B1; =B1-Time(4,0,0) in C1

example:
<table x:str="" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="32" width="480"><col style="width: 85pt;" width="113"><col style="width: 143pt;" width="190"><col style="width: 74pt;" width="99"><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 85pt;" x:num="1288846934594" align="right" height="17" width="113">1.28885E+12</td> <td class="xl24" style="width: 143pt;" x:num="40486.209891134262" align="right" width="190">Thursday, November 04, 2010</td> <td class="xl25" style="width: 74pt;" x:num="40486.043224467598" width="99"> 1:02:15 AM</td> </tr></table>Thanks again. Hope this helps others also.

Tim

 

[Ruddles]

I think you need a few more zeroes on Ruddles suggested 86400...

D'oh! I actually did that when I tested it!

 

[barry houdini]

If you separate into two cells like that then you might also need to use the -"4:00" in the date cell, otherwise if the original time is between 00:00 and 04:00 then the time will be adjusted correctly....but the date will be one too many, so in B1 try

=A1/86400000+DATE(1970,1,1)-"4:00"

and format as date

and in C1

=MOD(B1,1)

format as time