How to: Store and Retrieve Date Values in Excel Ranges

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Application-level projects Microsoft Office version Excel 2003 Excel 2007 For more information, see Features Available by Application and Project Type.

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

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

Using 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.

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

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

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

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

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

To retrieve a date value from a named range

• Retrieve the date value from NamedRange1.

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

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

Using Native Excel Ranges

To store a date value in a native Excel range object

1. Create a Range that represents cell A1.

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

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

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

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

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

To retrieve a date value from a native Excel range object

• Retrieve the date value from rng.

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

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

See Also

Tasks

How to: Send Values to Worksheet Cells

How to: Refer to Worksheet Ranges in Code

How to: Add NamedRange Controls to Worksheets

Concepts

Working with Ranges

Excel Object Model Overview

NamedRange Control

The Variable missing and Optional Parameters in Office Solutions