1 Reply Latest reply on Aug 29, 2016 2:35 PM by philipHPG

    Calendar Conditional format 1 or 2 evaluates true for any number with 1 or two in it

    moralmunky

      Following the guidance of this site, I created a calendar to display a set of color coded calendars to show where project due dates fall. The first events of a project, the photoshoot, normally spans across multiple days and I use the following conditional format formula to change the date green if it falls on the beginning, end and in-between dates. The dates on the mini calendars are calculated numerical variables.

       

      Month ( Mini Calendar 2::firstOfMonth1 ) = Month ( Retouching_Calendar::Shoot Start )  and Self  ≥  Day ( Retouching_Calendar::Shoot Start )  and  Self   ≤  Day ( Retouching_Calendar::Shoot End )

       

      First I make sure the the date falls within the particular iteration of the mini calendar month, then get the day number or the first and last day of the photoshoot evaluating true if the are equal two or between the day numbers.

       

      The issue I am encountering is if the date range includes the first, second or third of the month, all the teens, 20's and thirty are evaluating as true as well. Any suggestions?

       

      Because the photoshoot starts on the 3rd of October, the 30th and 31st are also formulating true because they have a 3 in them.

      Screen Shot 2016-08-29 at 4.50.30 PM.png

       

      In this example, because the photoshoot starts on the 1st and includes the 2nd and third, all dates are evaluating true.

      Screen Shot 2016-08-29 at 4.51.52 PM.png

      In this examples the  the photoshoot is the 9th through the 13, but 9, 10, 11, 12 or 13 are not calculating true. If I change the start from 1/9 to 1/10, then 10, 11, 12 and 13 highlight as intending.

      Screen Shot 2016-08-29 at 4.51.06 PM.png

       

      In this example the photoshoot is the 26th through the 29th and the conditional formatting works as intended

      Screen Shot 2016-08-29 at 4.50.00 PM.png