Importing Excel Dates in D365 F&O through X++ without the Apostrophe Trick

 We often get a requirement to create excel upload custom functionality in x++ . In this post we will see how to handle Excel OLE Automation date serial numbers in X++ so your users never have to format a cell before uploading again.

/// <summary>

/// Converts an Excel cell value to a formatted date string.
/// Handles both cases:
///   1. OLE Automation serial number (e.g. "46145") — Excel stores dates this way
///   2. Already a formatted date string (e.g. "03/06/2026")
///
/// Why strLen == 5:
///   OLE dates from year 1900 onwards produce 5-digit integers.
///   Any real date formatted as text (dd/mm/yyyy, yyyy-mm-dd, etc.)
///   is always longer than 5 characters.
///   This makes strLen == 5 a reliable discriminator.
///
/// Why any2date(str2int(...)):
///   str2int() parses the string to an X++ integer (e.g. 46145)
///   any2date() interprets that integer as an OLE Automation date
///   and converts it to an X++ date.
///   This is the same conversion Excel performs internally when
///   displaying the number as a date.
/// </summary>
private str getDateFromString(str _dateStringField)
{
    if (strLen(_dateStringField) == 5)
    {
        // OLE serial number detected
        // Convert: "46145" → int 46145 → X++ date
        date convertedDate = any2date(str2int(_dateStringField));

        // Return as formatted string for further processing
        // Note: the - 2 adjustment is explained below in the pitfalls section
        return strFmt("%1", DateTimeUtil::date(convertedDate) - 2);
    }
    else
    {
        // Already a formatted date string — return as-is
        return _dateStringField;
    }
}

/// 

/// Same logic — returns an X++ date type directly instead of a string.
/// Use this when the target field is a date type, not a string type.
/// 
private date getDate(str _dateStringField)
{
    date convertedDate;

    if (strLen(_dateStringField) == 5)
    {
        convertedDate = any2date(str2int(_dateStringField));
    }
    // If not a 5-char string, convertedDate remains dateNull()
    // Caller should handle the dateNull() case

    return convertedDate;
}

How any2date() works with OLE serial numbers

Handling datetime columns — the System.DateTime.FromOADate approach

// For datetime columns where the OLE value has a decimal component
// (time component stored as fraction of a day)
// Example: 46145.375 = 03/06/2026 09:00:00

real pDateval;
utcdatetime pDatetimeval;

// Read the cell value as a real number (handles the decimal time portion)
pDateval = any2real(range.get_Item(i, 2).value);

// Convert OLE date (real/double) to .NET DateTime using CLR interop
// System.DateTime::FromOADate() is the exact inverse of
// the OLE Automation date encoding
System.DateTime postingDateTime = System.DateTime::FromOADate(pDateval);

// Convert .NET DateTime to D365 FO UTC datetime
pDatetimeval = Global::clrSystemDateTime2UtcDateTime(postingDateTime);

// Extract just the date component if needed
invoiceIntegration.PostingDate = DateTimeUtil::date(pDatetimeval);

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class ImportAccessorialInvoice
{
    private ItemId findItem(str _SiteBucket, str _InvoiceType)
    {
        return CustomItemMapping::findItemByParm(MappingType::Invoice, _SiteBucket, _InvoiceType);
    }
    private str getDateFromString(str _dateStringField)
    {
        if(strLen(_dateStringField) == 5)
        {
            date convertedDate = any2date(str2int(_dateStringField));
            return strFmt("%1",  DateTimeUtil::date(convertedDate) - 2);
        }
        else
            return _dateStringField;
    }
    private date getDate(str _dateStringField)
    {
        date convertedDate;
        if(strLen(_dateStringField) == 5)
        {
            convertedDate = any2date(str2int(_dateStringField));
        }

        return convertedDate;
    }
    void run()
    {
        real                                                        pDateval;
        utcdatetime                                                 pDatetimeval;
        System.IO.Stream                                            stream;
        ExcelSpreadsheetName                                        sheeet;
        FileUploadBuild                                             fileUpload;
        DialogGroup                                                 dlgUploadGroup;
        FileUploadBuild                                             fileUploadBuild;
        FormBuildControl                                            formBuildControl;
        COMVariantType                                              type;
        POInvoiceInterface                                          invoiceIntegration , invoiceIntegrationdel;
        Dialog                                                      dialog =    new Dialog("@ImportPOInvoice");
        dlgUploadGroup          = dialog.addGroup('@SYS54759');
        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), "@InvoiceUpload");
        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');
        str COMVariant2Str(COMVariant _cv)
        {
            switch (_cv.variantType())
            {
                case COMVariantType::VT_BSTR:
                    return _cv.bStr();
                case COMVariantType::VT_EMPTY:
                    return '';
                default:
                    throw error(strfmt('@SYS26908', _cv.variantType()));
            }
        }
        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
            if (fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int                         rowCount, i,columncount,j;
                    Package.Load(stream);
                    ExcelWorksheet   worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange    range       = worksheet.Cells;
                    rowCount           = (worksheet.Dimension.End.Row) - (worksheet.Dimension.Start.Row)  + 1;
                    columncount      = (worksheet.Dimension.End.Column);
                    ttsbegin;
                    delete_from invoiceIntegrationdel where invoiceIntegrationdel.HasValidationErrors==NoYes::Yes;
                    for (i = 2; i<= rowCount; i++)
                    {
                        invoiceIntegration.clear();
                        POInvoiceIntegrationInterface        pOInvoiceIntegrationCounter;

                        select maxof(RowNum) from pOInvoiceIntegrationCounter
                            index hint RowNumIdx;
                        invoiceIntegration.RowNum      = pOInvoiceIntegrationCounter.RowNum + 1;
                        invoiceIntegration.Invoice = range.get_Item(i, 7).value;
                        invoiceIntegration.LocationID = range.get_Item(i, 9).value;
                        invoiceIntegration.CarrierName = range.get_Item(i, 3).value;
                        invoiceIntegration.CarrierProNumber = range.get_Item(i, 4).value;
                        invoiceIntegration.InvoiceDate = this.getDateFromString(range.get_Item(i, 8).value);
                        invoiceIntegration.LoadNumberFile = range.get_Item(i, 6).value;
                        invoiceIntegration.AccessorialsLessFuelSurcharge = range.get_Item(i, 10).value;
                        invoiceIntegration.ACCode  =   range.get_Item(i, 13).value;
                        invoiceIntegration.ACDescription   = range.get_Item(i, 14).value;
                        invoiceIntegration.Department      = range.get_Item(i, 15).value;
                        invoiceIntegration.CostCenter      = range.get_Item(i, 16).value;
                        invoiceIntegration.Purpose         = range.get_Item(i, 17).value;
                        invoiceIntegration.VendAccount     = range.get_Item(i, 1).value;

                        pDateval = any2real(range.get_Item(i, 2).value);
                        System.DateTime postingDateTime = System.DateTime::FromOADate(pDateval);
                        pDatetimeval = Global::clrSystemDateTime2UtcDateTime(postingDateTime);
                        invoiceIntegration.PostingDate = DateTimeUtil::date(pDatetimeval);

                        invoiceIntegration.AccessoriaApprovalNumber = range.get_Item(i, 5).value;
                        invoiceIntegration.AccessoriaApprovalNumber = invoiceIntegration.AccessoriaApprovalNumber ? invoiceIntegration.AccessoriaApprovalNumber : PurchParameters::find().CustomDefaultApprovalNumber;
                        invoiceIntegration.ItemId                   = this.findItem(range.get_Item(i, 11).value, range.get_Item(i, 12).value);
                        invoiceIntegration.PurchPrice               = range.get_Item(i, 10).value;

                        // Generate Row Number
                        invoiceIntegration.validateAccessorialInvoice();
                        if(invoiceIntegration.PurchPrice)
                        {
                            invoiceIntegration.insert();
                        }

                    }
                    ttscommit;
                    info("@ImportCompleted");
                }
            }
            else
            {
                error("@ImportError");
            }
            this.openStagingInterfaceForm();
        }
    }
    public static void main (Args args)
    {
        ImportPOInvoice importInvoice = new ImportPOInvoice();
        importInvoice.run();
    }
    private void openStagingInterfaceForm()
    {
        Args            args = new Args();
        MenuFunction    menuFunction;
        menuFunction = new MenuFunction(
                        menuItemDisplayStr(InterfaceForm1),
                        MenuItemType::Display);
        menuFunction.run(args);
    }
}


The - 2 adjustment — what it is and when you need it

You may have noticed this line in getDateFromString():

return strFmt("%1", DateTimeUtil::date(convertedDate) - 2);


The - 2 is a date correction that accounts for two known quirks in the OLE Automation date system:

1. The 1900 leap year bug: Excel incorrectly treats 1900 as a leap year and 
includes February 29, 1900 in its day count — a date that never existed. 
This adds 1 extra day to all Excel OLE dates from March 1900 onwards.

2. Epoch difference: The X++ any2date() function and the Excel OLE system may use a slightly 
different epoch start in certain version combinations, adding another 1-day offset.


The strLen == 5 boundary — what about years before 2000?

A question worth addressing: what if someone imports a date from before year 2000 where the OLE serial number is a 
4-digit number?

Date
OLE serial
strLen
Handled by
01/01/1995
34700
5
✅ getDateFromString() — 5-char check catches it
01/01/1999
36161
5
✅ getDateFromString() — correctly detected
01/01/2000
36526
5
✅ getDateFromString() — correctly detected
31/12/2099
73050
5
✅ getDateFromString() — correctly detected
01/01/1900
1
1
❌ Falls through to else — treated as date string
01/01/1970
25569
5
✅ Correctly detected


For all practical business date scenarios — any date from 1995 onwards — the OLE serial number is always 5 digits. Dates before 
approximately 1927 produce 4-digit serials, but these are outside any realistic AP invoice date range. The strLen == 5 check is 
safe for all real-world financial document dates.

Why this pattern is better than asking users to format cells

The apostrophe approach — Problems in practice users generally forget. Every time someone gets a fresh copy of the template 
or opens it in a different version of Excel, the cell format may reset.When cells are formatted as Text, Excel shows a green warning 
triangle on every cell. Users call this a bug.Pasting dates from another system into a text-formatted cell in Excel pastes the raw text
with the apostrophe visible — producing values like '03/06/2026 in the import.When the template is filled by an automated 
system or another X++ export, it will never add apostrophes — breaking the entire import pipeline.

The code-side approach — Why it is correct users upload the file exactly as Excel saved it — no cell formatting required.Works 
whether the user typed the date, pasted it, or it was generated by another system.Works for both date and datetime columns using 
two different but equally clean conversion paths.The logic is in the import class where it belongs — not in a user instruction 
document that no one reads.


Conclusion :-

The reason Excel date cells come through as 5-digit numbers in X++ EPPlus imports is well-understood: Excel stores dates as 
OLE Automation serial numbers, and EPPlus reads the raw underlying value rather than the formatted display string. The fix is
entirely on the code side.

For pure date columns, any2date(str2int(cellValue)) converts the serial number back to an X++ date in two function calls. For 
datetime columns where the time component matters, System.DateTime::FromOADate(any2real(cellValue)) followed by 
Global::clrSystemDateTime2UtcDateTime() handles the full precision conversion through CLR interop.

The strLen == 5 check is the discriminator that makes both approaches safe — it detects OLE serials reliably 
for all dates from 1995 to 2099 without any false positives against real date strings. Test the - 2 epoch adjustment in your own
environment before going to production, and your users will never need to touch a cell format again.