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

 

[jasonb75]

Just a thought with the 4 hour difference, is daylight savings time (or local equivilant) a factor that needs to be considered?

 

[nitebyfly]

Yes, it is. Thank you for pointing that out. I went back and checked and at least for here in Florida, there would be a difference of -5 hours or -4 hours. Is there a way to include that in the original formula?

Thanks again,

Tim

 

[jasonb75]

It can, but I don't think easily.

I'll see if I can come up with something, but hopefully barry will have a swift solution

 

[barry houdini]

Well, the rules for daylight saving have changed over the years so it depends on the date ranges of your data - are you looking at just "current" and future dates, e.g. 2010 onwards or do you need to include historical data?

If it's the latter then you probably need to construct a lookup table with historical daylight savings times, if the former then you could also use a lookup table or include some additional logic in the formula......

 

[jasonb75]

Can you use VBA solutions?

Looking at the "formula logic"

http://www.sqldrill.com/excel/showthread.php?t=383334&p=1327654#post1327654

Maybe a VBA solution would be easier to work with.

http://www.cpearson.com/excel/LocalAndGMTTimes.htm

 

[barry houdini]

You can simplify the formula solution a little, modifying my suggestion here

So if you have the original data in A2 then you can use this formula in B2 to convert to GMT/UTC

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

then you can do a further conversion in C2 to convert to EST, taking into account daylight saving time with the current rules

=B2-LOOKUP(B2,DATE(YEAR(B2),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(B2),{1,3,11},7))+{0,7,6}/24,{5,4,5})/24

As the current rules (in the US) came into effect from 2007 that would be valid for all data from 2007 and into the future (until the rules change again).

 

[nitebyfly]

Wow - not a formula I would have come up with. Thanks again. I bestow great praises upon you. ray:

Tim