At Alternate Address

[doughemi]

Some of my members are snowbirds who go to Florida or Arizona for the winter. Some have a summer home in the mountains where they go in the summer months.

I have fields which contain this alternate address, as well as the start mm/dd and end mm/dd text fields for their stay at the alternate address. These rarely change from year to year. There are startDate and EndDate date fields as well.

An AtAltAddrFlag simply calculates if the current date is greater than the start date, and less than the end date.

The problem is how to calculate the correct year for the start and end date fields. If Joe Snowbird goes to FL on November 15, I would use 11/15/11 for the start date for this season. But if he goes on January 15, then the correct start date is 1/15/12.

You can see that the same questions would occur regarding the end date, along with whether the end mm/dd is before or after the start mm/dd.

Is there any way other than a whole slew of nested case() statements in the date calculations to end up with a correct calculation for AtAltAddrFlag?

[comment]

I am going to simplify this using only months:

Case (

startMonthonth < endMonth ;

startMonth ≤ currentMonth and currentMonth ≤ endMonth ;



startMonth ≤ currentMonth and currentMonth ≤ 12

or

1 ≤ currentMonth and currentMonth ≤ endMonth

)

[doughemi]

Thanks, Michael; very elegant.

Once I realized that the day was only important if the start month or the end month was the same as the current month, it was relatively simple to incorporate it into your calculation:

Case (

startMonth < endMonth ;

startMonth ≤ currentMonth and currentMonth ≤ endMonth ;



startMonth ≤ currentMonth and currentMonth ≤ 12

or

1 ≤ currentMonth and currentMonth ≤ endMonth

)



and



Case(currentMonth = startMonth and currentDay<startDay;0;

currentMonth = endMonth and currentDay > endDay;0;

1

)