CODEWITHSUNDEEP
c#exceldatetimespreadsheetgear
Jimmy
Jimmy

Reputation: 2106

Spreadsheetgear: Error converting to Datetime

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.

Upvotes: 0

Views: 585

Answers (1)

Tim Andersen
Tim Andersen

Reputation: 3184

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);

Upvotes: 1

Related Questions