asked on

Convert Epoch number value to date and time string

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

Saurabh Singh Teotia

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

barry houdini

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

jodymichael

ASKER

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

zorvek (Kevin Jones)