Convert Epoch number value to date and time string

jodymichael
jodymichael used Ask the Experts™
on
I have a table that i am extracting into excel. One column contains a work log with numerous occurences within a text string where the 10 digit epoch time value is displayed. I am able to pull each one for a conversion, however before i write anything i need to determine a calculation method within VBA to convert the Epoch time value of XXXXXXXXXX into the date and time value of xx/xx/xxxx xx:xx:xx

Has anyone got a function in VBA for excel that will perform this conversion for me and provide me with a text string to visually display the epoch time value as dd/mm/yyyy hh:mm:ss?

Thanks in advance, Jody
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
I'm bit confused here, You said 10 digits letter to be converted to dd/mm/yyyy hh:mm:ss, However if i count the length of that then the length required for it is 14 that is dd-->2 + mm-->2+yyyy-->4+hh-->2+mm-->2+ss-->2 =14
Saurabh
Most Valuable Expert 2013

Commented:
Saurabh,
I believe the epoch time value will be a number, i.e. number of seconds from 1/1/1970 or similar.
Jody can you give an example epoch number and what date and time you want it to convert to because time zones also come in to play.
Do you absolutely need VBA you can usually do it with a simple formula, i.e. something like
=DATE(1970,1,1)+A1/86400
where A1 is epoch number
As I said, that might need tweaking depending on the format of the number and/or timezone but that'll give you the general idea
regards, barry

Author

Commented:

Hi Barry,
One example that I have is "1246363902" which converts to "30/06/2009 13:11:42".
The reason I need it in VB is that it is going out to end-user, and I need the build function to activate on a data refresh.
The update currently lasts 3 to 4 seconds, and if I can get the right calculation in VB to address this then I can avoid in sheet calculations which mess up when they cut and paste data for their own use, and I expect with what I am running I may bump an extra two or three seconds onto it which is bearable :-)
Appreciate your response, thanks
Jody
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2013
Commented:
My formula appears to be 1 hour out so to adjust by an hour and convert to text this worksheet formula would give the correct value

=TEXT(DATE(1970,1,1)+A1/86400+"1:00","dd/mm/yy hh:mm:ss")

VBA's not my forte, perhaps Saurabh can give you the VBA version.....
Top Expert 2008
Commented:
Public Function Epoch(TheDate As Date) As String
    Epoch = Format(DateSerial(1970, 1, 1) + TheDate/86400 + TimeSerial(1, 0, 0), "dd/mm/yy hh:mm:ss")
End Function

Kevin

Author

Commented:
Fantastic Gentlemen! thats is great, both solutions work for insheet and VBA.

Thank you both for your efforts.

Most apreciated

Jody

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial