Solved

Convert Excel Serial Date Number

Posted on 2004-08-24
5
577 Views
Last Modified: 2012-08-13
Hi,

I have a number of dates that are stored as Excel Serial Dates

eg:

38401 = 18/02/2005
38321 = 30/11/2004

i converted these using the format cells (Custom -> dd/mm/yyy) functionality in excel

how do i convert them in vb.net? will it be a combination of the format function? ( MsgBox(Format(38401, "dd/mm/yyyy"))  doesnt work )

Regards

DSE
0
Comment
Question by:DSE
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Desp
ID: 11879522
i dont think that you can use MsgBox(Format(38401, "dd/mm/yyyy"))  here ..

there should be a home numeric value
38401 = 18/02/2005
38321 = 30/11/2004
....
...
38000

what is that?
0
 

Author Comment

by:DSE
ID: 11879546
38000 = 14/01/2004
0 = 00/01/1900 (obviously a bit dodgy as there isnt a day 00... but thats what excell converts it to)
0
 
LVL 19

Assisted Solution

by:Desp
Desp earned 51 total points
ID: 11879813
Try this:

        Dim HomeDate As DateTime = DateTime.Parse("01/01/1900")
        dim SerialNumber as Int32 = 38000' Replace 38000 with any value
        MsgBox(HomeDate.AddDays(SerialNumber).ToString("dd/mm/yyyy"))  
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 250 total points
ID: 11879817
use the FromOADate and ToOAdate methods

like

        Dim f As Date = New Date(2004, 1, 14)
        MsgBox(f.ToOADate())
        MsgBox(f.FromOADate(38000))
0
 

Author Comment

by:DSE
ID: 11880266
thx both for your comments... ultimately ronalds exhaustive knowledge has show the proper way to do it.
Ive given some points to Desp because i believe that the suggestion would also work.

Regards

DSE
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question