CODEWITHSUNDEEP
c#excel
corymathews
corymathews

Reputation: 12619

Excel Date Field Conversion Problem

I currently have an excel sheet with one of the columns being in the date format.

What I see when I open up the spreadsheet is something like 12/29/09 and the program sees 40176.

I figured out this is the value present when I change the column to general text.

My question is how can I read the value 12/29/09 instead of 40176 or how can I change 40176 into a valid date?

My program is in c# Must be read in in c#

Here is sample code of my connection if it helps any.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
string myPath = @"C:\Test.xls";
excelApp.Workbooks.Open(myPath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);

 Microsoft.Office.Interop.Excel.Sheets sheets = excelApp.Worksheets;
 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
 excelApp.Visible = true;

 if(((Microsoft.Office.Interop.Excel.Range)excelApp.Cells[r, 1]).Value2 != null)
     DateString = ((Microsoft.Office.Interop.Excel.Range)excelApp.Cells[r, 1]).Value2.ToString();

Upvotes: 4

Views: 7993

Answers (4)

Durai Amuthan.H
Durai Amuthan.H

Reputation: 32310

you could use the Text function.

Syntax

Text(Value,FormatText)

Example

1)I put the 40176 in a excel A2 2)then i refrence it in the formula below

 =TEXT(A2,"mm/dd/yy")

on A3

Then the value comes 12/29/09. just like you asked

Caution:

If you put single quote instead of double quote it mightn't work

Upvotes: 0

mobilius
mobilius

Reputation: 11

Use the excel TEXT(val,format) function.

Example:

In cell A2 enter =today() and you'll get a number like 40189 if you enter into cell A2

If you enter =TEXT(today(),"mm/dd/yyyy") you'll get todays date formatted as text and it will look like "01/30/2012"

Upvotes: 1

Ant
Ant

Reputation: 5225

As Reed Copsey said, the DateTime.FromOADate() method will convert the value into a DateTime. If, however, you want 12/29/09 as a string, and don't want to manipulate it any further, you can use cell.Text instead.

Upvotes: 2

Reed Copsey
Reed Copsey

Reputation: 564631

You can use DateTime.FromOADate() to convert the double into a DateTime value.

Upvotes: 12

Related Questions