How to correctly identify if a cell value is a DateTime

[Qish]

Hi,

I have read a number of postings, including the following that allows
me to convert an optional parameter (declared as a type of object)
into a DateTime.

http://exceldna.codeplex.com/Thread/View.aspx?ThreadId=236897

I have created this function to identify if parameter is a DateTime:

public static bool isDateTime(object param)
{
try
{
DateTime tempDate;
if (param is double)
{
tempDate = DateTime.FromOADate((double)param);
return true;
}
else if (param is string)
{
tempDate = DateTime.Parse((string)param);
return true;
}
else
return false;

}
catch (Exception e)
{
return false;
}
}

The problem is that I would like to create a function in Excel DNA
that can take many optional parameters and I won't know at compile
time what the data types of the optional parameters will be, therefore
I have declared them as objects. Here is the function header:

public static object myFunction(
[ExcelArgument(Name = "[Parameter 1]", Description =
"Optional parameter")] object param1,
[ExcelArgument(Name = "[Parameter 2]", Description =
"Optional parameter")] object param2,
.....
[ExcelArgument(Name = "[Parameter 20]", Description =
"Optional parameter")] object param20

So in my code I can test each paramX to see if it is a DateTime. The
trouble is that ANY double (in fact ANY number at all) that is passed
as a parameter is identified as a DateTime. So if I put 10 into a cell
and pass that into myFunction then my code will see that as
DateTime(10th Jan 1900), not as double(10).

I want to avoid the call to myFunction having to include delimiters
around dates or something to identify each parameter type.

If you have any suggestions on how I can handle this I'd be very
grateful to hear them.

Many thanks,
Qish

[Govert van Drimmelen]

Hi Quish,

Internally, Excel does not have a DateTime data type. What you see in
the sheet is just a special number format applied to a double value.
(There are also no other numeric types, like int, in Excel.)
So I doubt you'll find a perfect solution. Something like
=myFunction("5 January 2010" + 1) might be really hard to deal with.
Other than retrieving and parsing the whole formula yourself, I know
of no way to distinguish this from =myFunction(40180).
Excel has some special handling for certain internal functions like
=NOW(), but generally this aspect is problematic.

Your approach might differ for values typed into the parameters, and
for a reference to another cell containing the date value. By marking
your ExcelArgument as AllowReference=true you will get the actual
reference to the cell as an ExcelReference type and not just the
value, and can sometimes act accordingly.

Maybe:
1. For a reference you could convert the cell contents to a string
using xlCoerce, and then parse the string.

2. For a reference you could try to read the numeric format string
using option 7 of xlcGetCell (your function must then be marked with
IsMacroType=true). Then you have to parse the number format and figure
out whether it is a date/time format. This sounds painful to me.

3. For a value you could try to see whether the DateTime you get from
your check is in a reasonable range for the dates you expect, but of
course this is not great if you also have numbers that could be in
that range.

I would suggest you re-think your function interface so that you don't
have to deal with this problem at all. It might be better to
automatically generate a family of functions than to have such a
generic one where you have to figure out the parameter types based
purely on what is passed in...?

-Govert

[Qish]

Hi Govert,

Thanks for your response. I had feared this would be the answer.

My function is being used to call stored procedures. What I'm trying
to avoid is the need to write separate functions and recompile every
time I add a stored proc to the database so I wanted to allow the end-
user to specify the name of the stored proc in the call to the
function and then the parameters of any type. I'll look at option 1
and maybe 2 but failing that it'll be back to the drawing board.......

Thanks,
Qish