4 Replies Latest reply on Oct 11, 2012 10:53 AM by dkensinger

    Conditional formatting of date

    dkensinger

      Title

      Conditional formatting of date

      Post

           I'd like to format a date field like this:

           if the date is less than 60 days from today, show the dd-mmm-yyyy

           if the date is less than 1 year away and greater than 60 days from today, show the mmm-yyyy

           if the date is more than a year from today, show Q1-yyyy, Q2-yyyy etc.

           Can this be done with a script or function?

           Dave

        • 1. Re: Conditional formatting of date
          philmodjunk

               A Calculation field can be set up with the case function to return the three different options.

               The first two options could be done with two copies of the same date field-each with different data formatting and then conditional formatting could make the contents of one field visible and the other invisible, but since the last format is not a standard date format, the last one could not be implemented in this fashion.

          • 2. Re: Conditional formatting of date
            dkensinger

                 Thanks, PhilModJunk.

                 I figured either a nested IF, ELSE structure would work or a CASE structure would work - but I wondered in there was an easy way to format the date other than the built in conditional formatting that only affects the color or the font.

                 As for the Q1-yyyy format, it is an option in the normal date format area to get something like that - but I want to control it "conditionally"

                 I'll try a few of those options and let you know what works best.

                 cheers,

                 Dave

            • 3. Re: Conditional formatting of date
              philmodjunk

                   Conditional formatting is not limited to the color or the font--you can also change the style and font size, changing the font size to a size greater than 100 is how you can make the contents of a field disappear. But this is an option that I indicated would only work for the first two formats. The alternative, that works with all three formats, is to set up a calcualtion field with Case to do the formatting:

                   Let ( [ d = YourDateField ;
                             m = Month ( d ) ;
                             dy = Day ( d ) ;
                             y = Year ( d ) ;
                             Q = ceiling ( m / 3 )
                           ];
                           Case ( d < Get ( CurrentDate ) + 60 ; Right ( "0" & dy ; 2 ) & "-" & right ( "0" & m ; 2 )  ;
                                     y < year ( Get ( currentDate ) ) + 1 ; right ( "0" & m ; 2 )  ;
                                     Q
                                    ) & "-" & y
                          )

                   select 'text' as the return type and click storage options to make this an unstored calculation field.

              • 4. Re: Conditional formatting of date
                dkensinger

                     Thanks again, PhilModJunk!

                     I'll give that a try and see how it looks. I wish I could embed some python code in there (it makes more sense to me) but I'll take what I can get to make this work.

                     cheers,

                     Dave