Better method to convert from Excel OLE Automation Date

#1

All,

Does anyone have a more elegant method to convert an Excel OLE Automation Date (e.g. 43983.XXX) into a DateTime in Dundas? The data source is a Microsoft Form Excel backend, which gets read in as a string. I then convert it to a double and do the script below:

DateTime datezero = new DateTime(1899,12,30);
DateTime ConvertedDate = datezero.AddDays($OADate$);
return ConvertedDate

There is a method in .NET to do this (FromOADate and ToOADate) but they are not available in the Dundas library. All of the date conversion documentation in Dundas is if the date was already in a string and could be translated using a mm/dd/yyyy format variation.

#2

Hi @christopher.simpson,

Can you run a convert statement on your database? Something like this might help you…

https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154121

#3

Hi Jeff,

It is an Excel Online sheet that records all of the O365 Form responses. No DB to run a script against.

#4

Hi @christopher.simpson,

I tested this in an example myself and it seems that the Calculated Element does allow you to convert from OADates. Here is my test.

Create a DataInput in a DataCube with 2 OADates.

image
image.png763×572 9.35 KB

Add the following calculated element.

image

See the converted output.

image
image.png1186×734 22.2 KB

#5

Thanks Jeff! I had the wrong syntax with DateTime. Because of that Dundas was kicking it out saying unknown method, making me think Dundas didn’t have it.

#6

Great! Glad it’s working.