?
Solved

Convert Excel Serial Date Number

Posted on 2004-08-24
5
Medium Priority
?
582 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:Fahad Mukhtar
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:Fahad Mukhtar
Fahad Mukhtar earned 204 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 1000 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

Independent Software Vendors: 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

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…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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