9 Replies Latest reply on Jan 13, 2012 9:56 AM by ahutler

    Conditional Formatting for dates

    ahutler

      Title

      Conditional Formatting for dates

      Post

       I have a field that is a date field that automatically populates the date that a new record was created.

      #1 I would like the capacity to manually type the date into this field, (is this possible, even if it is a past date?)

      #2 I would like this field to be bold/in red/with a yellow background once it has been three years after creation date.

      Example 01/12/2015

        • 1. Re: Conditional Formatting for dates
          philmodjunk

          #1. Try editing the field and see what happens. If this is the auto-enter field option for entering a creation date, you should still be able to edit the field to change the auto-entered date to a different value. Note that an auto-entered modification date will automatically update with today's date any time you change any field in the record.

          #2 You could use this conditional format expression: ( ( Get ( CurrentDate ) - YourTable::YourDateField ) / 365 ) > 3

          or:

          Let ( [ T = Get ( CurrentDate ) ; D = YourTable::YourDateField ] ;
                    ( Year ( T ) - Year ( D ) - ( Date ( Month ( T ) ; Day ( T ) ; Year ( D ) ) ) < D ) > 3
                )

          The second expression handles the extra leap year day better. (

          • 2. Re: Conditional Formatting for dates
            ahutler

             Thats great, and it worked but if there is no date in this filed, it still highlights it as yellow. Is there any formula to make it so it only highlights if there is a date in this field and three years from that date have past.

            Also, I changed other fileds in the new record and it didnt update to the new date??? (which is good and what I want unless I wasn't understanding your statement.)

            • 3. Re: Conditional Formatting for dates
              philmodjunk

              Add : YourTable::YourDateField AND //put the above expression here

              Then the expression will not be true unless there is a date in the date field.

              There are two auto-enter date options discussed in my last post. If you specify "creation date". Then the current date is entered into the field and it will stay unchanged unless you or a script modify it. This appears to be the option you specified. If you specify "modification date", then any change to any field in the record will update the date with today's date.

              Which option is correct depends on the needs of your database design.

              • 4. Re: Conditional Formatting for dates
                ahutler

                 Well I tried the above, and it isn't highlighting if there is no date, but now, it is highlighting every record that has a date, regardless if it is 3 years past or not.

                Formula is below

                ((Table::Arrived at RAM) /  ( ( Get ( CurrentDate ) - Table::Arrived at RAM ) / 365))  > 3

                • 5. Re: Conditional Formatting for dates
                  ahutler

                   Could someone assist with this issue?

                   Well I tried the above, and it isn't highlighting if there is no date, but now, it is highlighting every record that has a date, regardless if it is 3 years past or not.

                  Formula is below

                  ((Table::Arrived at RAM) /  ( ( Get ( CurrentDate ) - Table::Arrived at RAM ) / 365))  > 3

                  • 6. Re: Conditional Formatting for dates
                    philmodjunk

                    You seem a bit impatient. I access the forum off an on from 9:00 am to 4:30 pm Pacfic Standard Time. I didn't see your posts until a few moments ago.

                    Use this expression:

                    (Table::Arrived at RAM) AND ( ( Get ( CurrentDate ) - Table::Arrived at RAM ) / 365))  > 3

                     

                    / is the division symbol so your were dividing the numver value of your date by the value of the second expression.

                    • 7. Re: Conditional Formatting for dates
                      ahutler

                      I apologize if I seem impatient.

                      I copied/pasted your expresion. And it highlights the >3 and then a pop-up comes up saying, "An operator (e.g. +, -,*,...) is expected here.

                      ????

                       

                      • 8. Re: Conditional Formatting for dates
                        philmodjunk

                        Table::Arrived at RAM AND (( Get ( CurrentDate ) - Table::Arrived at RAM ) / 365)  > 3

                        There was an extra ) after the 365.

                        • 9. Re: Conditional Formatting for dates
                          ahutler

                           It worked.

                          Thank you