Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
  [Expand]Reporting
  [Expand]Chart Control
  [Expand]Grid View
  [Expand]Card View
  [Expand]Vertical Grid
  [Collapse]Spreadsheet
   [Expand]Product Information
   [Collapse]Concepts
    [Expand]Spreadsheet Document
     Supported Formats
     Import and Export
    [Collapse]Cell Basics
      Cell Data Types
      Dates and Times in Cells
      Error Types
      Cell Referencing
      Formatting Cells
    [Expand]Spreadsheet Formulas
     Defined Names
     Data Validation
    [Expand]Data Presentation
    [Expand]Charting
     Printing
    [Expand]Mail Merge
     Protection
     Keyboard Shortcuts
   [Expand]Visual Elements
  [Expand]Rich Text Editor
  [Expand]Image and Data Browsing
  [Expand]Docking and Popups
  [Expand]Site Navigation and Layout
  [Expand]File Management
  [Expand]Multi-Use Site Controls
  [Expand]Scheduler
  [Expand]HTML Editor
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gauges
  [Expand]Data Editors
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Localization
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Dates and Times in Cells

Dates and times are stored in cells as numbers. Thus, the values of cells that contain dates and times are of the numeric type. A number that specifies a date and time consists of the date (integer part) and time (fractional part) components. The CellValue.NumericValue property returns this number.

To display a number as a date and time, apply the required date and time format to a cell via the Formatting.NumberFormat property. The CellValue.DateTimeValue property returns the System.DateTime object, which specifies the date and time that is represented by the number contained in a cell.

To determine whether or not a cell displays its numeric value as date and time, use the Cell.IsDisplayedAsDateTime property.

Expanded Dates in Cells

SpreadsheetControl stores dates as numbers that are called serial values.

A serial value is an integer that is the number of elapsed days from the first day in the date system. SpreadsheetControl supports the following date systems for serial values:

  • The 1900 date system. The first date is January 1, 1900, and its serial value is 1. The last date is December 31, 9999, and its serial value is 2,958,465.

    This date system is used in the workbook by default.

  • The 1904 date system. The first date is January 1, 1904, and its serial value is 0. The last date is December 31, 9999, and its serial value is 2,957,003.

    To use this date system in the workbook, set the Workbook.DocumentSettings.Calculation.Use1904DateSystem property to true.

The serial value of the date contained in a cell is the integer part of the number that the CellValue.NumericValue property returns. You can input a date into a cell in one of the following ways:

  • Set the Range.Value property to a number that specifies the date's serial value in the 1900 or 1904 date system.
  • Set the Range.Value property to a System.DateTime object. The date's serial value will be calculated in the 1900 date system.
  • Set the Range.Value property to an object that is returned by the CellValue.FromDateTime method. The date's serial value will be calculated based on the passed use1904DateSystem parameter value.
  • Set the Range.Formula property to an expression that returns a date. The date's serial value will be calculated according to the date system used in the workbook.

To obtain the date that corresponds to the serial value contained in a cell, use the CellValue.DateTimeValue property. This date is defined according to the date system used in the workbook. In other words, the CellValue.DateTimeValue property specifies the date that will be displayed in a cell when your apply a date format.

For example, consider the 12/17/2012 date. The table below demonstrates which serial values this date is represented by in each date system, how the date can be assigned to a cell in each date system, and how the date displayed depends on the date system applied in the workbook.

Date System to Input Date

Serial Value of 12/17/2012

Input Date

Use1904DateSystem = false

Use1904DateSystem = true

1900

41260

cell.Value = new DateTime(2012, 12, 17)

CellValue.NumericValue = 41260

CellValue.DateTimeValue = {12/17/2012 12:00:00 AM}

CellValue.NumericValue = 41260

CellValue.DateTimeValue = {12/18/2016 12:00:00 AM}

1904

39798

cell.Value = CellValue.FromDateTime(new DateTime(2012, 12, 17), true);

CellValue.NumericValue = 39798

CellValue.DateTimeValue = {12/16/2008 12:00:00 AM}

CellValue.NumericValue = 39798

CellValue.DateTimeValue = {12/17/2012 12:00:00 AM}

Expanded Times in Cells

SpreadsheetControl stores times as decimal fractions that range from 0 to 0.99999, which represent the time range from 12:00:00 AM to 11:59:59 PM. The cell's time value is the fractional part of the number that the CellValue.NumericValue property returns.

To obtain the time that corresponds to the decimal fraction of the cell's CellValue.NumericValue number, use the CellValue.DateTimeValue property. This time will be displayed in a cell when you apply a date format.

Expanded Date and Time Display Formats

To display a number as a date and time, associate the appropriate display format with the cell via the Formatting.NumberFormat property. Use the Range.BeginUpdateFormatting-Range.EndUpdateFormatting method pair to access and modify the Formatting.NumberFormat for the cell range.

The Cell.DisplayText property returns the value as it is displayed in a cell.

This example demonstrates how to specify different display formats for date and time values in cells.

The image below shows the results.

Note

If a number is negative or excessively large (greater than the last date in the date system used), it will be displayed as ##### in the cell when you apply the date and time format.

Expanded See Also

How would you rate this topic?​​​​​​​