Solved

Convert Excel Serial Date Number

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
More on Time zones in vb 2010 12 38
Footer for each row on Gridview 2 19
.NET 2008 VB and C# 6 27
Calculate number of nights between two dates 5 17
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now