DateTimes and Excel Serial Numeric Dates SpreadsheetGear API Sample

Description

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.

Source Code

// 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;