3 Replies Latest reply on Apr 30, 2009 9:12 AM by TSGal

    Conditional Formatting in Date fields

    snkm

      Title

      Conditional Formatting in Date fields

      Post

      Hello,

       

      I have 2 date fields let say date_start and date_end.  I need the formatting of the date_end field to change depending on the number of days between the two fields.

       

      Example:

       

      date_start is populated with 4/1/09 and date_end is populated with 4/11/09.  Since they are ten days apart, I want the date_end to appear in red text.

       

      You dig?:)

        • 1. Re: Conditional Formatting in Date fields
          philmodjunk
            

          Date_end - date_start will give you the number of elapsed days. Use that in your conditional format expression.

           

          (table::date_end - table::date_start) > 10

           

          You can think of date fields as number fields with special formatting when you use them in an expression.

          • 2. Re: Conditional Formatting in Date fields
            snkm
              

            That worked thanks!  Is there possibly a way to make it only count business days? (M-F)

            • 3. Re: Conditional Formatting in Date fields
              TSGal

              snkm:

               

              Thank you for your post.

               

              Yes, you can count only the business days.  Let me give you the formula for the condition first, and then the explanation.

               

              Let (

                 [ a = date_start + Middle ( "1000002" ; DayOfWeek ( date_start ) ; 1 ) ;

                 b = date_end - Middle ( "2000001" ; DayOfWeek ( date_end ) ; 1 ) ;

                 dowa = DayOfWeek ( a ) ;

                 dowb = DayOfWeek ( b ) ;

                 businessdays = Int ( ( b - a ) / 7 * 5 ) + Mod ( dowb - dowa ; 7 ) - If ( dowb < dowa ; 2 ; 0 ) ] ;

                 If ( businessdays ≥ 10 ; 1 ; 0 )

              )

               

              -------------

               

              Although I assume date_start and date_end are weekdays, there is always the chance that someone will enter a weekend date.  Therefore, the first two lines ensure this.  Take date_start for example.  If it is currently a Sunday, we add 1 day.  If Monday through Friday, we add zero days.  If Saturday, we add 2 days.  For date_end, if it is a Sunday, we subtract 2 days, zero days for Monday through Friday, and 1 day for Saturday.  These results are put into temporary variables "a" and "b".

               

              Now that the date_start and date_end values are ensured as weekday dates (a and b), the values for the day of the week are stored into variables "dowa" and "dowb".  This will become apparent next.

               

              Next, we determine the number of business days.  For each week, we want to return 5 days instead of 7.  Therefore, we subtract the two dates from one another, divide by 7 and multiply by 5.  Take the integer value of that.  Now, we subtract the day of week of the date_start from the day of week of the date_end.  If the day of week of the end_date is less than the day of week of the date_start, then a weekend exists between the two dates, and 2 days are subtracted.

               

              Since I'm using the day of week twice in the formula, that is the reason why the variables dowa and dowb are created.

               

              Now that the business days are known, our final formula checks to see if the business days are 10 or greater.  If so, return 1; else return zero.

               

              If you need clarification for any of the above steps, please let me know.

               

              TSGal

              FileMaker, Inc.