Recursive Date List Function

[rothdavid]

I have a project where I need to make a value list of dates based on start date and end date.  Once I have a value list I can then select a value position based on other criteria.

 

a date range on the record may be:  (start) 1/1/2014  to  4/15/2014 (end)

 

The list created would always be for the first day of each month for the duration of the date range:

 

1/1/2014

2/1/2014

3/1/2014

4/1/2014

 

I'd like this to occur as a calc or CF, I'd rather not script this to create a variable because there may be a few hundred needed to generate.

 

Thanks,

 

David

 

[comment]

I would use a repeating calculation field for this =

Let ( [
start = Extend ( StartDate ) ;
d = Date ( Month ( start ) + Get ( CalculationRepetitionNumber ) - 1 ; 1 ; Year ( start ) )
] ;
Case ( d ≤ Extend ( EndDate ) ; d )
)

The advantage being that the result type is Date. The (minor) disadvantage is that you must pre-set the number of repetitions to the maximum expected duration in months.

 

Once you have this in place, create a self-join relationship matching on the record's unique id and define the value list to use the related values.

[Charity]

So you use the related values based on id to constrict the dates otherwise a regular value list would show dates for all records.  Is this correct, Comment?  

Never mind. I should just test it.  sorry to bother.