How to Parse Excel Dates in D365 F&O Using X++

When importing data from Excel into Dynamics 365 Finance & Operations (D365 F&O), dates can come in different formats. Depending on how the Excel file was created or how EPPlus reads the cell, the value may be returned as a System.DateTime, a numeric Excel serial date, or a string.

The following helper method handles these common scenarios and converts the Excel value into an X++ date.

X++ Code

private static date parseExcelDate(OfficeOpenXml.ExcelRange _cells, int _row, int _col)
{
    System.Object   cellValue = _cells.get_Item(_row, _col).Value;
    date            result    = dateNull();

    if (cellValue == null)
    {
        return result;
    }

    System.Type     valueType = cellValue.GetType();
    str             typeName  = valueType.FullName;

    if (typeName == 'System.DateTime')
    {
        System.DateTime dateTime = cellValue;
        result = mkDate(dateTime.Day, dateTime.Month, dateTime.Year);
    }
    else if (typeName == 'System.Double' ||
             typeName == 'System.Int32' ||
             typeName == 'System.Decimal')
    {
        // Cell holds a raw OLE Automation date serial number
        real            oaDate   = any2real(cellValue);
        System.DateTime dateTime = System.DateTime::FromOADate(oaDate);

        result = mkDate(
            dateTime.Day,
            dateTime.Month,
            dateTime.Year);
    }
    else
    {
        try
        {
            result = str2Date(any2Str(cellValue), 321);
        }
        catch
        {
            warning(strFmt(
                "Could not parse date value at row %1, column %2 (type: %3).",
                _row,
                _col,
                typeName));

            result = dateNull();
        }
    }

    return result;
}

How It Works

The method first reads the Excel cell value and checks its .NET type.

• System.DateTime – Converts the .NET DateTime directly into an X++ date.

• Numeric values – Excel may store dates as OLE Automation serial numbers. System.DateTime::FromOADate() converts these values into a .NET DateTime.

• String values – Attempts to convert the value using str2Date().

• Empty or invalid values – Returns dateNull() and displays a warning when the value cannot be parsed.

Example

You can call the method while processing Excel rows:

date deliveryDate = parseExcelDate(cells, row, 5);

This approach is useful when building Excel import functionality in D365 F&O, especially when the same import file may contain dates represented in different formats.

Tip: Avoid assuming that an Excel date will always be returned as a string. Excel frequently stores dates internally as numeric serial values, which is why handling System.Double, System.Int32, and System.Decimal can prevent unexpected date conversion issues.