Data Import & Export

How Excel Stores and Identifies DateTime Cell Values (C# & VBA)

John Wilson Oct 23, 2026 868 views

Question details

The user needs to understand how Excel stores DateTime values internally and how to correctly retrieve or format these values programmatically via C#, COM, or VBA without encountering type conversion errors.

How Excel Stores and Identifies DateTime Cell Values

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Retrieving or writing date values programmatically using C# or VBA, where dates are incorrectly read as raw numeric types instead of formatted date strings.
Observed behavior: Excel returns a numeric serial value instead of the expected date string when querying a cell, leading to casting errors in C# or incorrect outputs during data export.

Before you start

Before handling date values programmatically, ensure you understand that spreadsheet applications store dates as numeric serial values starting from January 1, 1900, rather than raw text strings.

Solution 1Recommended

Safely Retrieve Date Values Using C#

Prevent casting errors by inspecting the runtime type of the cell value before converting it, rather than assuming it is a string.

Because Excel stores dates as doubles (serial numbers), casting a cell's value directly to a string in C# can crash your application or yield a raw number like '44200' instead of '01/01/2021'.

Read the raw cell value

Use the `.Value` or `.Value2` property on your COM interop Range object to extract the raw data from the cell.

Inspect the data type

Use the `is` operator or `GetType()` in C# to verify if the underlying data type is a `Double`.

Convert using FromOADate

If the value is a Double representing a date, use the `DateTime.FromOADate()` method in C# to convert the numeric serial value back into a standard C# DateTime object.

Format to string

Call `.ToString("MM/dd/yyyy")` or your preferred format on the resulting DateTime object to safely convert it into text.

Alternative property: If you only need the exact text as displayed on the screen, you can read the `.Text` property of the cell instead of `.Value`, though this is slower and depends on column width.

Solution 2

Force Dates to Store as Text Strings via VBA

If you explicitly need the cell to hold a text string rather than a serial date, you can preformat the cell or prefix the value in VBA.

Advanced Spreadsheet Automation

Handle Data Processing Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers robust support for COM automation, VBA macros, and advanced numeric formatting. You can efficiently manage, import, and export DateTime values using the same logic and APIs as you would in Microsoft Excel.

1. Launch the VBA Editor: Open WPS Spreadsheet and press Alt + F11 to launch the integrated VBA Editor.
2. Write your automation script: Insert a new module and write your automation script using standard `.Value`, `.Text`, or `.NumberFormat` properties.
3. Execute the macro: Run your macro to reliably format, calculate, and export your DateTime data as numeric values or text strings.

Fully compatible with Microsoft Excel VBA macros and COM interfaces.Accurate interpretation of Microsoft Office DateTime serial values.Lightweight and fast execution for programmatic data entry.Free to use with comprehensive data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel date show up as a 5-digit number like 44200 in C#?

Excel stores dates as numeric serial numbers counting the days since January 1, 1900. The number 44200 represents the underlying value, which Excel only displays as a date through visual number formatting. When querying `.Value` in C#, you receive the raw numeric serial.

What is the difference between .Value and .Text properties in COM automation?

The `.Value` (or `.Value2`) property returns the underlying data type of the cell (such as a Double for dates), whereas the `.Text` property returns a string matching exactly what is displayed on the screen, including any applied date formatting.

How do regional settings affect date interpretation in VBA?

When entering dates as text strings, the spreadsheet interprets the month and day based on your system's regional settings (e.g., MM/DD/YYYY vs DD/MM/YYYY). To ensure accurate data entry regardless of region, it is best to assign numeric values or use the DateSerial function in VBA.

Can WPS Office run my existing C# COM automation scripts?

Yes, WPS Office provides API interfaces similar to Excel, allowing many C# COM automation scripts and VBA macros to run with minimal modifications while maintaining high compatibility with Microsoft Office formats.

Excel & Data Problems