Creating Custom Sortable datatypes in excel

Question

I want to define a custom data type in excel 2007, a date range.

Basically, it's an array of dates or smaller date ranges like:

"{ 01-26-1991 , 01-01-2004 to 12-31-2004 , 12-13-2017 }"

which would define an arbitrary collection of dates, in the above case, january 26th 1991, the full year of 2004, and the 13th of december 2017.

I would like to be able to put the above syntax in a cell and have excel take care of management, so that i could call the cell.isWithin(otherDate) to check if the date is within the range specified, and allow the usual Excel pivot table to sort by the first date in the range.

I can write the code to do the above, including turning the data from a string to a custom object and back but am not sure of the hooks so that excel can see it as a datatype that can be sorted (by the earliest date) and so that any vba function i write won't have to create an object, parse the string into the object, modify the object, and generate an output string every time a value must be changed or queried.

Is there any method to allow this? or another technique that would let me do this at some vaguely sensible speed?

This datatype is going to be used to generate an odd custom gantt chart by using conditional formatting.

Answer 1

I'm not familiar with that sort of type passing in Excel, sorry.

But if your collection is arbitrary, I would approach it by storing a separate worksheet containing a table with your list of date ranges.

1            2
A  1991-01-26   1991-01-26
B  2004-01-01   2004-12-31
C  etc.

Then make a simple VBA function that takes a date argument and returns a boolean.

Function IsWithinDate(dCell as date) as Boolean
     // Loop to check against each date range in your table

     // Return TRUE or FALSE

         IsWithinDate = TRUE
End Function

Finally have your conditional formatting use the result of your function:

=IsWithinDate(A1)