Serial Number to Actual date Conversion

justinferns · March 22nd, 2012, 02:33 PM

Hi,

I need to Convert the serial number 1328207334 to the date
02/02/2012 1:28:53 PM.

First, I need to convert the serial number 1328207334 to 40941.56172 (General Format) and then to 02/02/2012 1:28:53 PM.

Can any one help me with a formula to do this?

Thanks!

JF

disel2010 · March 23rd, 2012, 05:20 AM

Hi,

looks like you're trying to convert an epoch time to a DateTime value.

Since the unix time Epoch is actually the number of seconds since 1st January 1970 you could use the following function:

Code:

Private Function EpochToDate2(epoch As Long) As Date
    Dim dt As Date
    dt = #1/1/1970#
    EpochToDate2 = DateAdd("s", epoch, dt)
End Function

Hope this helps..

justinferns · March 28th, 2012, 01:23 PM

Thanks Disel... That really helped... However, I noticed that I keep getting the hour value a little different.. Instead of 1 it shows 6 in the hour part of time..

Any idea why?

Thanks
JF

disel2010 · March 29th, 2012, 02:46 AM

Hi Justin,

looks like you still need to calculate your offset of GMT/UTC time.. (epoch time is defined as seconds since 1st January 1970 GMT/UTC midnight)

The DateAdd function can be used for that again:

Code:

 
DateAdd("h", hoursOffset, dt)

(hoursOffset would be 5 in your case)

justinferns · March 29th, 2012, 10:09 AM

Perfect thanks Disel!.. Amazing one! Great help!.. Thanks a ton! :)

JF

justinferns · March 30th, 2012, 12:08 PM

Hi Disel,

If you could help me with the reverse too.. As in how do I convert from Actual date to Serial date?

Thanks!
JF