Wrox Programmer Forums Serial Number to Actual date Conversion
 |
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

March 22nd, 2012, 02:33 PM
 justinferns Authorized User Join Date: Aug 2011 Posts: 24 Thanks: 0 Thanked 0 Times in 0 Posts
Serial Number to Actual date Conversion

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

March 23rd, 2012, 05:20 AM
 disel2010 Authorized User Join Date: Jan 2011 Posts: 86 Thanks: 1 Thanked 12 Times in 12 Posts

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..

March 28th, 2012, 01:23 PM
 justinferns Authorized User Join Date: Aug 2011 Posts: 24 Thanks: 0 Thanked 0 Times in 0 Posts

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

March 29th, 2012, 02:46 AM
 disel2010 Authorized User Join Date: Jan 2011 Posts: 86 Thanks: 1 Thanked 12 Times in 12 Posts

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:
```
(hoursOffset would be 5 in your case)

March 29th, 2012, 10:09 AM
 justinferns Authorized User Join Date: Aug 2011 Posts: 24 Thanks: 0 Thanked 0 Times in 0 Posts

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

JF

March 30th, 2012, 12:08 PM
 justinferns Authorized User Join Date: Aug 2011 Posts: 24 Thanks: 0 Thanked 0 Times in 0 Posts

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

 Similar Threads Thread Thread Starter Forum Replies Last Post Hard Disk Serial Number Asmatullah VB.NET 2002/2003 Basics 1 December 18th, 2007 04:26 PM how to get hard disk serial number in c# imran_mani .NET Framework 1.x 0 May 7th, 2007 03:45 AM Find Disk Serial Number M_Mentz Pro VB.NET 2002/2003 2 October 19th, 2005 08:18 PM Integrating a serial number madhukp VB How-To 4 March 22nd, 2005 09:15 AM Serial number Ned SQL Server 2000 6 November 6th, 2003 05:55 PM

 Contact Us - Wrox - Privacy Statement - Top