Jump to content

Calc with Return-Separated Range of Dates


This topic is 5733 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5733 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.