Programmatically store and retrieve date values in Excel ranges

You can store and retrieve values in a NamedRange control or a native Excel range object.

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

If you store a date value that falls on or after 1/1/1900 in a range using Office development tools in Visual Studio, 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.

Use a NamedRange control

• This example is for document-level customizations. The following code must be placed in a sheet class, not in the ThisWorkbook class.

To store a date value in a named range

1. Create a NamedRange control at cell A1.

   C#

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

   VB

   Dim NamedRange1 As Microsoft.Office.Tools.Excel.NamedRange = _
       Me.Controls.AddNamedRange(Me.Range("A1"), "NamedRange1")
   

2. Set today's date as the value for NamedRange1.

   C#

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

   VB

   Dim dt As DateTime = DateTime.Now
   NamedRange1.Value2 = dt
   

To retrieve a date value from a named range

1. Retrieve the date value from NamedRange1.

   C#

   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());
   

   VB

   Dim value As Object = NamedRange1.Value2
   
   If Not value Is Nothing Then
       If TypeOf value Is Double Then
           dt = DateTime.FromOADate(CType(value, Double))
       Else
           DateTime.TryParse(CType(value, String), dt)
       End If
   End If
   
   MessageBox.Show(dt.ToString())
   

Use native Excel ranges

To store a date value in a native Excel range object

1. Create a Range that represents cell A1.

   C#

   Excel.Range rng = this.Application.get_Range("A1");
   

   VB

   Dim rng As Excel.Range = Me.Application.Range("A1")
   

2. Set today's date as the value for rng.

   C#

   DateTime dt = DateTime.Now;
   rng.Value2 = dt;
   

   VB

   Dim dt As DateTime = DateTime.Now
   rng.Value2 = dt
   

To retrieve a date value from a native Excel range object

1. Retrieve the date value from rng.

   C#

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

   VB

   Dim value As Object = rng.Value2
   
   If Not value Is Nothing Then
       If TypeOf value Is Double Then
           dt = DateTime.FromOADate(CType(value, Double))
       Else
           DateTime.TryParse(CType(value, String), dt)
       End If
   End If
   System.Windows.Forms.MessageBox.Show(dt.ToString())
   

Related content

• Work with ranges
• Excel object model overview
• NamedRange control
• How to: Programmatically refer to worksheet ranges in code
• How to: Add NamedRange controls to worksheets
• Optional parameters in Office solutions