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.
In this example, because the photoshoot starts on the 1st and includes the 2nd and third, all dates are evaluating true.
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.
In this example the photoshoot is the 26th through the 29th and the conditional formatting works as intended
This is because, by default FileMaker treats expressions as strings. You want a numeric comparison. So change your conditional formatting to use GetAsNumber ( Self ) ≥ ...