Other API Samples

Live SpreadsheetGear API Samples

Workbook Miscellaneous DateTimes and Excel Serial Numeric Dates

Excel and SpreadsheetGear both store dates and date/times as serial numeric values, where a serial date value of 0 corresponds to the date "January 0, 1900 12:00:00 AM".* Dates prior to this are not supported. A serial date consists of two parts:

  • The whole number portion of a serial date represents the number of days elapsed since "January 0, 1900". For instance, a date of December 31, 2020 is the serial number 44196.
  • The fractional portion of a serial date represents the time of day. For instance the time 6:00 PM / 18:00 is 0.75)

In order for a cell to display its value as a date instead of the underlying serial numeric value, the cell's IRange.NumberFormat must be set to a date or date/time format such as "m/d/yyyy h:mm AM/PM".

SpreadsheetGear offers helper methods to convert .NET DateTime values to serial dates and vice-versa. The below sample code demonstrates the use of these methods.

*NOTE: although uncommon, Excel files do offer an alternative Date1904 mode where a serial numeric date value of 0 corresponds to January 1, 1904. This mode is typically not used for the most part, but SpreadsheetGear does support it if a workbook uses it.

// Create a new workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Setup a DateTime with a value of December 31, 2020, 6:00 PM (18:00).
System.DateTime dateTime = new System.DateTime(2020, 12, 31, 18, 0, 0);

// Set B1 to a specific date.  SpreadsheetGear will automatically convert the
// DateTime to its corresponding serial date value.  If the cell is not already
// setup with a date or date/time NumberFormat, SpreadsheetGear will apply a
// NumberFormat to the cell automatically.
cells["A1"].Value = "Input from .NET DateTime:";
cells["B1"].Value = dateTime;

// Construct a specific date and time using the Excel functions DATE(...) and
// TIME(...).  SpreadsheetGear will not automatically apply a date or date/time
// NumberFormat in this case, so we will apply on on our own to the cell.
cells["A2"].Value = "From from Formula:";
cells["B2"].Formula = "=DATE(2020, 12, 31) + TIME(18, 0, 0)";
cells["B2"].NumberFormat = "m/d/yyyy h:mm AM/PM";

// Convert numeric date to a DateTime using IWorkbook.NumberToDateTime(...).
double serialDate = 44196.75;
System.DateTime dateTimeVal = workbook.NumberToDateTime(serialDate);
cells["A3"].Value = "IWorkbook.NumberToDateTime() converts the serial date " +
    serialDate + $" to the .NET DateTime '{dateTimeVal:yyyy-MM-dd hh:mm:ss tt}'.";

// Convert DateTime to numeric date using IWorkbook.DateTimeToNumber(...).
dateTimeVal = new System.DateTime(2020, 12, 31, 18, 0, 0);
serialDate = workbook.DateTimeToNumber(dateTimeVal);
cells["A4"].Value = "IWorkbook.DateTimeToNumber() converts the .NET DateTime " + 
    dateTimeVal + $" to the serial date '{serialDate}'.";

// Make columns wide enough to fit their contents.
cells["A:A"].ColumnWidth = 24;
cells["B:B"].ColumnWidth = 20;
Run This Sample
Render Image

Generate an image representation of the results of this sample, which uses the SpreadsheetGear.Drawing.Image class to convert ranges, charts and shapes to images.

Render Image
Download File

Download an Excel Open XML Workbook (*.xlsx) file with the results of this sample.

Generate File
Related Samples
Back to Samples