Calc with Return-Separated Range of Dates

[Kent Searight]

Hi all,

I've been trying to figure out how to write this one for some time now and I'm stumped. Here's the scenario:

In a table I have a field called DaysClosed. It's a checkbox field showing all 7 weekdays. I have another field called Range. It holds a numerical value representing the number of weeks that the field DaysClosed is valid for.

What I want a calculation to do is return a carriage return separated value of the dates that occur on DaysClosed for the number of weeks in Range. An example would be:

Today is Thursday, 3/16/06. If the value in DaysClosed is:

Saturday

Sunday

and the Range is 3, then the calculation would return:

3/18/2006

3/19/2006

3/25/2006

3/26/2006

4/2/2006

4/3/2006

Any ideas on how to achieve this? Any and all help is greatly appreciated. Thanks in advance!

[Ender]

I haven't worked out the details, but I see two CFs here. The first to recursively parse the Days, and the second to recursively append the next N dates of each Day.

[comment]

I think this can be made very simple, if you only precompute the end date of your range. IOW, the CF will be in the format:

WeekDaysInRange ( startDate ; endDate ; weekDays )

and you will call it thus:

WeekDaysInRange ( Get (CurrentDate ) ; Get (CurrentDate ) + 7 * Range - 1 ; DaysClosed )

The function itself:

Case (

startDate and not IsEmpty ( FilterValues ( DayOfWeek ( startDate ) ; weekDays ) ) ;

startDate & ¶

)

&

Case (

startDate < endDate ;

WeekDaysInRange ( startDate + 1 ; endDate ; weekDays )

)

Note that I use NUMBERS to indicate the DayOfWeek, so if you want to keep it simple, change your value list to "1¶2¶3¶4¶5¶6¶7", make your selection field tall and narrow, and place a text label with day names next to it.

---

BTW, in my calendar, Apr 3 2006 is a Monday...

[Kent Searight]

Hi comment,

Thanks for the response. For the life of me, I can't get it to work. The only values I get are 3/17/2006 (today), when I indicate that the business is closed on days 6 & 7, and 3/18/2006 when I indicate closed on day 7.

Calc field result is set to Date, all other field results are set properly and all dependent fields are populated...any idea as to what I might be doing wrong?

[comment]

If you're using Get (CurrentDate ), the calc must be unstored.

If that's not it, attach your file.

[Kent Searight]

Here ya go!

If you say it works, I believe you. I went back and gave it another look and I still can't find anything wrong.

Why do I get the feeling you're going to point out something really simple I overlooked? :qwery:

DatesClosed.zip

[comment]

1. Mind the order of parameters in the custom function.

2. daysClinicClosed is a list - therefore must be of type text.

3. Most important - the result of the custom function is a return separated list. That too must be of type text.

DatesClosed.fp7.zip

Edited March 17, 2006 by Guest