Dates and Times in Cells
- 5 minutes to read
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 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.
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 CellRange.Value property to a number that specifies the date’s serial value in the 1900 or 1904 date system.
- Set the CellRange.Value property to a DateTime object. The date’s serial value will be calculated in the 1900 date system.
- Set the CellRange.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 CellRange.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} |
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.
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 CellRange.BeginUpdateFormatting-CellRange.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.
using DevExpress.Spreadsheet;
// ...
IWorkbook workbook = ASPxSpreadsheet1.Document;
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F1"].Formula = "= Now()";
// Apply different date display formats.
worksheet.Cells["A1"].NumberFormat = "m/d/yy";
worksheet.Cells["B1"].NumberFormat = "d-mmm-yy";
worksheet.Cells["C1"].NumberFormat = "dddd";
// Apply different time display formats.
worksheet.Cells["D1"].NumberFormat = "m/d/yy h:mm";
worksheet.Cells["E1"].NumberFormat = "h:mm AM/PM";
worksheet.Cells["F1"].NumberFormat = "h:mm:ss";
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.