Spreadsheetgear: Error converting to Datetime

Question

I'm trying to parse a cell value in excel sheet and convert it to Datetime using following code.But I'm getting an error as :

'arguments.CurrentWorksheet.Workbook.NumberToDateTime(inpDate)' threw an exception of type 'System.ArgumentOutOfRangeException' base: {"Specified argument was out of the range of valid values.\r\nParameter name: Invalid date-time serial number."} ActualValue: null Message: "Specified argument was out of the range of valid values.\r\nParameter name: Invalid date-time serial number."

Code:

double inpDate = arguments.GetNumber(2);
startDate = arguments.CurrentWorksheet.Workbook.NumberToDateTime(inpDate);

where inpDate in the sheet is 20170113.

Could you advise please?

Thanks.

Can you check the value of inpDate before the conversion? I think it is null — Mihai Ovidiu Drăgoi
Hi Mihai...I debugged earlier and value of inpDate is 20170113.0.Thanks. — Jimmy
Oh, that means Tim's response is probably correct. Good luck! — Mihai Ovidiu Drăgoi

Tim Andersen Tim Andersen · Accepted Answer · 2017-02-27T16:02:23

Your inpDate is not a valid "Excel date." Dates/times are stored in SpreadsheetGear and Excel as a double representing a date/time serial number. Whole numbers represent days, with the value 1 being January 1, 1900 and the fractional portion of the number represents the time (i.e., 0.5 is noon). So, for instance, a value of 42793.0 represents February 27, 2017 (12:00 AM). You can read more about Excel dates on the following informative page: http://www.cpearson.com/excel/datetime.htm

IWorkbook.NumberToDateTime(...) expects this sort of serial date, but your input of '20170113' (which I assume is supposed to represent Jan 13, 2017) is getting interpreted as a date way in the future, like somewhere close to the year 57,160 (20,170,113 days from Jan 1, 1900), which is too far out to be a valid date in Excel/SpreadsheetGear.

For your particular case, it seems more appropriate to use DateTime.ParseExact(...), such as:

DateTime dateTime = DateTime.ParseExact("20170113", "yyyyMMdd", CultureInfo.CurrentCulture);

Spreadsheetgear: Error converting to Datetime

1 Answers