Solved

Convert Excel Serial Date Number

Posted on 2004-08-24
5
580 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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