Handling Date Columns When Importing Excel Files in C#

Introduction

If you’ve ever worked on a C# application that imports Excel files, chances are you’ve encountered headaches with date columns. Date values in Excel aren’t always as straightforward as they appear on the surface. Depending on how users input and format dates, you might end up dealing with a mix of date types, numeric values, or strings.

In this post, I’ll walk you through why this happens, what issues it causes, and how you can reliably handle date columns when importing Excel files in C#. We’ll look at solutions using two of the most popular C# libraries for working with Excel: EPPlus and ClosedXML.

---

The Problem with Date Columns in Excel

Excel internally stores date values as numbers, specifically as OLE Automation Dates, where:

• 1 = January 1, 1900 (or 1899-12-31 depending on platform).
• Date cells are actually stored as double values representing the number of days since that base date.

• Depending on user formatting or third-party software exporting Excel files, a date cell might come through as:

  • A DateTime value.
  • A double (Excel date number).
  • A string (if explicitly typed or formatted as text).

This inconsistency makes it tricky for developers to consistently read date columns when importing data.

---

The Solution: Detect and Convert Gracefully

When reading Excel cells containing dates, your approach should:

1. Check if the cell value is already a DateTime.
2. If not, check if it’s a numeric value (double) and convert it using DateTime.FromOADate().
3. If it’s a string, attempt to parse it via DateTime.TryParse().
4. Handle invalid or empty values gracefully.

---

📦 Approach 1: Using EPPlus

📌 Install EPPlus

dotnet add package EPPlus

📌 Sample Excel Content

Name	BirthDate
Alice	2025-06-30
Bob	45678 (Excel date number)

📌 Code Example

using OfficeOpenXml;
using System;
using System.IO;

var filePath = "sample.xlsx";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

using (var package = new ExcelPackage(new FileInfo(filePath)))
{
    var worksheet = package.Workbook.Worksheets[0];
    int rowCount = worksheet.Dimension.Rows;

    for (int row = 2; row <= rowCount; row++)
    {
        var name = worksheet.Cells[row, 1].Text;
        var birthDateCell = worksheet.Cells[row, 2];
        DateTime birthDate;

        if (birthDateCell.Value is DateTime)
        {
            birthDate = (DateTime)birthDateCell.Value;
        }
        else if (double.TryParse(birthDateCell.Text, out double oaDate))
        {
            birthDate = DateTime.FromOADate(oaDate);
        }
        else if (DateTime.TryParse(birthDateCell.Text, out DateTime parsedDate))
        {
            birthDate = parsedDate;
        }
        else
        {
            birthDate = DateTime.MinValue;
        }

        Console.WriteLine($"{name} - {birthDate:yyyy-MM-dd}");
    }
}

📌 What’s Happening:

• Check if the value is already a DateTime.
• Fallback to numeric conversion.
• Fallback to string parsing.
• Assign DateTime.MinValue if parsing fails.

---

📦 Approach 2: Using ClosedXML

📌 Install ClosedXML

dotnet add package ClosedXML

📌 Code Example

using ClosedXML.Excel;
using System;
using System.Linq;

var workbook = new XLWorkbook("sample.xlsx");
var worksheet = workbook.Worksheet(1);
var rows = worksheet.RangeUsed().RowsUsed();

foreach (var row in rows.Skip(1)) // Skip header
{
    var name = row.Cell(1).GetString();
    var birthDateCell = row.Cell(2);
    DateTime birthDate;

    if (birthDateCell.DataType == XLDataType.DateTime)
    {
        birthDate = birthDateCell.GetDateTime();
    }
    else if (double.TryParse(birthDateCell.GetValue<string>(), out double oaDate))
    {
        birthDate = DateTime.FromOADate(oaDate);
    }
    else if (DateTime.TryParse(birthDateCell.GetValue<string>(), out DateTime parsedDate))
    {
        birthDate = parsedDate;
    }
    else
    {
        birthDate = DateTime.MinValue;
    }

    Console.WriteLine($"{name} - {birthDate:yyyy-MM-dd}");
}

📌 What’s Happening:

• Use DataType property to check if it’s a date.
• Convert numeric OLE Automation date numbers.
• Parse string dates as fallback.
• Gracefully handle invalid data.

---

Conclusion

Handling date columns in Excel files can be a subtle and frustrating problem for C# developers. The core issue lies in how Excel stores and formats date values internally, often leading to inconsistent data types when importing.

By implementing a defensive, multi-step approach — first checking for DateTime, then numeric double, and finally parsing strings — you can reliably import date columns using either EPPlus or ClosedXML.

If your application involves heavy Excel interaction, wrapping this logic into a helper method or service class is highly recommended for code reuse and maintainability.

---

📌 Bonus Tip:

You can also inspect the cell’s number format string (e.g., cell.Style.Numberformat.Format) to decide whether to treat it as a date if needed, though it’s usually safer to stick with the value-type-based approach.

---

💡 Further Reading:

• EPPlus Documentation
• ClosedXML Documentation
• .NET DateTime.FromOADate Method

---

Thanks for reading! If you’ve run into Excel date import issues before, share your war stories in the comments.