[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do you convert Excel Date type to DateTime in .NET?

Posted on 2007-07-25
6
Medium Priority
?
2,990 Views
Last Modified: 2013-12-17
am using the Office Primary Interop Assemblies (automation) to access an
Excel file from .NET and get cell contents. I'm using C#.

When I get the cell contents that contains an Excel Date value, I am having
trouble converting from the object variable type to DateTime.
The following error is displayed:

               The specified cast is invalid.

Here's the code with error:

Range rng = (Range)oSheet.get_Range("A4", Type.Missing);
System.Object myvalue = rng.Cells.Value2; //Assign it to an object var
DateTime dt = (DateTime)myvalue;

So how do I cast the retrieved cell contents to .NET DateTime?
0
Comment
Question by:TALJr
6 Comments
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19572142
0
 

Author Comment

by:TALJr
ID: 19572173
How does this apply to what I described?
It looks like it solves a problem different from what I asked.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19572248
you can parse the input string from the excel file as described in the link
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 19572315
Dear TALJr,
You can write your own custom format accordingly regarding the datetime that retrieved from cell content. Anyway, you can amend it based on the cell value that you retrieved.
eg:
//myvalue.ToString() = 2007-7-26 1:00:PM <-- your cell content value for example.
DateTime dt=Format(CDate("2007-7-26 1:00:PM"), "dd,MM yyyy hh:mm:tt")
For more details regarding custom format date, you can check here:
http://www.dotnetspider.com/qa/Question23642.aspx
0
 

Author Comment

by:TALJr
ID: 19575144
The myvalue.ToString()  value is not even close to the format you have given as an example. I don't have it right now( (I'm out of my office) but it is a large integer: something like  127879.
Would this be days after some start date? It is definitely not seconds or milliseconds.

This is not .NET DateTIme, but Excel Date type.

-TALJr

0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1500 total points
ID: 19575241
Excel uses the serial date concept, based on 1/1/1900.  The value for 1/1/1900 is 1.  Today's date is 39289.

You need a function like this:

public static DateTime DateSerialToDate(int value)
{
    DateTime dt = new DateTime(1900, 1, 1);
    return dt.AddDays(value - 2);
}

Bob
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
The PowerShell Core 6.0 of .NET release is just the beginning. The upcoming PowerShell Core 6.1 would have artificial intelligence and internet of things capabilities. So many things to look forward to in the upcoming release.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

590 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