Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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
 
Old March 22nd, 2012, 02:33 PM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old March 23rd, 2012, 05:20 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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..
 
Old March 28th, 2012, 01:23 PM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 29th, 2012, 02:46 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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)
 
Old March 29th, 2012, 10:09 AM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

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

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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.