3 Replies Latest reply on Jul 24, 2014 2:48 PM by raybaudi

    Exclude year in date range calculation

    JimBessette

      Title

      Exclude year in date range calculation

      Post

           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?  
           THANKS!

           Let (
                   [
                       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?