Exclude year in date range calculation
Maybe there's an easier way to do this, but I'm trying to find anyone on a tour who's birthday happens to be during the tour. I obviously don't care about the year, so I tried a calculated field as shown below. My test case is a tour from May 25 through June 8. There is only one person whose birthday falls within those dates, and my calculation works for him, and works for all those with birthdays not within the dates, except for one. His birthday is 6/19 and the filed is set to 1 (true) when it shouldn't be. Can anyone spot a problem in this calculated field?
TourMonthStart = Month (Tour::StartDate );
TourMonthEnd = Month (Tour::EndDate );
TourDayStart = Day (Tour::StartDate );
TourDayEnd = Day (Tour::EndDate );
TourStart = TourMonthStart & "/" & TourDayStart;
TourEnd = TourMonthEnd & "/" & TourDayEnd;
ClientBirthMonth = Month(Client::Birthdate);
ClientBirthDay = Day(Client::Birthdate);
ClientBirthMonthDay = ClientBirthMonth & "/" & ClientBirthDay
If ( ClientBirthMonthDay > TourStart and ClientBirthMonthDay < TourEnd; 1 ; 0 )
Just one more thought. Is it possible it's not comparing as a date, and 6/19 is < 6/8? Only thing I can think of. Is there a way to force it to act like a date? Maybe do a text as date conversion?