Dynamics Community Forum Thread Details

Excel Date Conversion Fails During Import (Mixed Formats)

🔹 Problem Statement

I am importing Excel data into D365 FO using X++ (reading via range.get_Item(i, j).Value).

I am facing an issue where the same date field (TransDate EDT) is coming in different formats depending on the Excel file, causing conversion failures.

🔹 Sample Data

✅ File 1 (Working)

Invoice Date = 12-31-2025

Format: MM-DD-YYYY

Value is coming as string
❌ File 2 (Failing)
```
Invoice Date = 45868
```
- Format: Excel serial date
- Value is coming as numeric (Real)
  🔹 Current Code
  
  transactiondate = range.get_Item(i, j).Value;
  
  🔹 Attempted Solution
  
  public static TransDate getExcelDate(anytype _value)
  
  {
  
      TransDate result;
  
      if (typeOf(_value) == Types::Real || typeOf(_value) == Types::Integer)
  
      {
  
          result = DateTimeUtil::date(
  
              DateTimeUtil::addDays(DateTimeUtil::minValue(), any2int(_value) - 2)
  
          );
  
      }
  
      else
  
      {
  
          result = str2Date(any2Str(_value), 123);
  
      }
  
      return result;
  }
  
  ⚠️ Issue
  
  Returning NULL for both formats
  
  str2Date() fails due to format mismatch
  
  Numeric conversion using DateTimeUtil::minValue() is incorrect