Meaning of exception in C# app: "Not a legal OleAut date"?

Does anyone know what this means. Getting this in C# winforms applications:

Not a legal OleAut date

6 Answers

It means that somewhere in the program is attempting to convert to or from an OLE Automation Date outside the valid range 1-January-4713 BC to 31-December-9999 AD. It might have slipped through because OLE Automation Dates are represented as a double.

Start by looking for any uses of the methods:

DateTime.FromOADate

DateTime.ToOADate

An OADate is represented as a double value whose value is the number of days from midnight on 30 december 1899 (negative values representing earlier dates).

This exception is thrown when trying to convert a value that is outside the valid range of Ole Automation dates to/from a .NET DateTime value (methods DateTime.FromOADate and DateTime.ToOADate - which are also used implicitly for COM Interop).

I believe to be valid for conversion to an OADate the .NET DateTime value needs to be strictly greater than 01/01/0100.

To convert from OADate to a .NET DateTime value, the double value needs to be strictly greater than -657435 (= 01/01/0100) and strictly less than 2958466.0 (01/01/10000).

It means you provided an invalid date somewhere, attempting to convert to or from an OLE Automation date outside the valid range 1-January-4713 BC to 31-December-9999 AD. A possible cause is that it might have slipped through because OLE Automation Dates are represented as a double.

I've used:

try { if (folderItem.ModifyDate.Year != 1899) { this.FileModifiedDate = folderItem.ModifyDate.ToShortDateString() + " " + folderItem.ModifyDate.ToLongTimeString(); } } //we need this because it throws an exception if it's an invalid date... catch (ArgumentException) { } 

to deal with the same problem I'm having. It throws the exception when we check the year in my case. Doing nothing on an invalid date is exactly the behavior I want, so this hack works.

What I found was that a column with a large row_id '257381195' was attempting to be read by Excel as a Date. What I ended up doing was altering that column's data to a string by preceding the row_id with a single quote. This resolved my issue. Hope this helps.

I had the same problem. When trying to parse date from cell.

When converting string to double, the decimal separator was ignored because of my localization.

So the cell value string:"123456,78" was converted to double:12345678 instead of double:123456.78 (made-up example value used)

Here is my snippet that fixed the issue:

if (double.TryParse( s: cell.CellValue.InnerText, style: NumberStyles.Number, provider: CultureInfo.InvariantCulture, result: out double oaDateValue)) { DateTime parsedDateTime = DateTime.FromOADate(oaDateValue); // DateTime was parsed successfully. } else { // DateTime failed to parse. } 

The important part here is CultureInfo.InvariantCulture. This makes sure that if that cell has a decimal separator . or , it will be detected and parsed properly.

If you still get the error make sure that cell.CellValue.InnerText is an actual value and not a reference to SharedStringTable. If that's the case you can use this helper extension method that will help extract the value from SharedStringTable:

private static string GetCellValue(this Cell cell, SharedStringTable sharedStringTable) { if (cell.DataType == null || !cell.DataType.HasValue) { return cell.CellValue != null ? cell.CellValue.InnerText : String.Empty; } else if (cell.DataType.Value == CellValues.SharedString) { return sharedStringTable .ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText; } return cell.CellValue.InnerText; } 

Now you can use cell.GetValue(sharedStringTable) to get the actual string value.