Solved

Convert Excel Serial Date Number

Posted on 2004-08-24
5
581 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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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