4 Replies Latest reply on Aug 22, 2014 9:54 AM by Vicky

    Conditional Formatting based on number of days

    Vicky

      Title

      Conditional Formatting based on number of days

      Post

           Hi,

           I have a date field called ETD that users will use to enter in the estimated time of departure. My boss wants ETD to change colors, red for when the date is unconfirmed and green after we confirm the date.  He wants ETD to automatically change to red every 10 days to remind him to send an email to confirm.  ETD will change back to green when we either click an ok button or if we change the date.  I understand that we use conditional formatting to change the color of the fields, but I am unsure of what formula to use to keep track of the number of days that have passed.  If there is another way to change the color of the fields please let me know and I'll try it.

           Thanks

            

        • 1. Re: Conditional Formatting based on number of days
          philmodjunk

               To compute the number of days between two dates, subtract them.

               Get ( CurrentDate ) - ETD

               will return the number of days from the date in ETD to the current date.

               (Date fields store an integer that represents the number of days from 12/31/0000 to the date shown in the field and that's why we can sometimes treat date fields just like number fields.)

          • 2. Re: Conditional Formatting based on number of days
            Vicky

                 Sorry I was unclear with what I was trying to describe. Here is an example:

                 ETD is 9/23 and the day I entered info in the field is 8/22.  He wants ETD to change to red on 9/1, 9/11, and 9/21. If on 9/3 someone changes the ETD to a later date in September, then filemaker will start counting again from 9/3 making ETD change to red on 9/13 and 9/23.

                 Is what I want to do even possible?

            • 3. Re: Conditional Formatting based on number of days
              philmodjunk

                   Yes this is possible and I was waiting for a bit more detail before answering more than your specific question. It's important that these be actual dates and not text fields with just the month and day specified but what you want can be set up.

                   Not Mod ( Get ( CurrentDate ) - YourTable::ETD ; 10 )

                   will work as your conditional format expression to specify a format change on every 10th day in the interval between the computer's date in its system clock and the date recorded in ETD.

                   (Mod is simply a function that divides the first term by the second and returns the remainder. Remember your school days trudging through long division computing a quotient and a remainder? It will return zero in this example every time the number of elapsed days is a multiple of 10. The Not operator then reverses the logic so that a result of 0 evaluates as True instead of False.)

              • 4. Re: Conditional Formatting based on number of days
                Vicky

                     Thank you so much for your help and explanation!