5 out of 8 rated this helpful - Rate this topic

How to: Store and Retrieve Date Values in Excel Ranges

Visual Studio 2005

Note
Some code examples in this topic use the this or Me keyword or the `Globals` class in a way that is specific to document-level customizations, or they rely on features of document-level customizations such as host controls. These examples can be compiled only if you have the required applications installed. For more information, see Features Available by Product Combination.

If you store a date value that falls on or after 1/1/1900 in a Microsoft Office Excel 2003 range using Microsoft Visual Studio 2005 Tools for the Microsoft Office System, it is stored in OLE Automation (OA) format. You must use the FromOADate method to retrieve the value of OLE Automation (OA) dates, If the date is earlier than 1/1/1900, it is stored as a string.

Note
Excel dates differ from OLE Automation dates for the first two months of 1900. There are also differences if the 1904 date system option is checked. The code examples below do not address these differences.

To store a date value in a named range

Create a NamedRange control on cell A1.

Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
    this.Controls.AddNamedRange(this.get_Range("A1", missing), "NamedRange1");

Set today's date as the value for NamedRange1.

DateTime dt = DateTime.Now;
NamedRange1.Value2 = dt;

To retrieve a date value from a named range

Retrieve the date value from NamedRange1.

object value = NamedRange1.Value2;

if (value != null)
{
    if (value is double)
    {
        dt = DateTime.FromOADate((double)value);
    }
    else
    {
        DateTime.TryParse((string)value, out dt);
    }
}
MessageBox.Show(dt.ToString());

Compiling the Code

This code must be placed in a sheet class, not in the ThisWorkbook class.

How to: Store and Retrieve Date Values in Excel Ranges

To store a date value in a named range

To retrieve a date value from a named range

Compiling the Code

See Also

Tasks

Concepts

Other Resources

Community Additions