9 Replies Latest reply on Nov 23, 2009 11:59 AM by daveealex

    Change Date into letter

    daveealex

      Title

      Change Date into letter

      Post

      I have a client that does not like the expiration date to be seen. He would only like a letter M to show up when the time has past 2 years. Right now I have created the same thing but with dates using conditional formatting(box with date changes to red after 2 years). Is there any way for me to enter a date and the date change to an empty box for 2 years and then change to an M on the expiration date(or even a date box and the M box side by side and the date and m change together)? I know this is hard to understand(even for me(and I think him too)) he is an older gentleman and is kind of set on seeing the M in the box. In case you are wondering the M stands for Moroso in Italian(it means not paid in English).

      Thank you in advance

       

      daveealex

        • 1. Re: Change Date into letter
          ninja
            

          Howdy daveealex,

           

          Use the same calculation you are using for your conditional formatting, but put it into an if statement on an "M" field.

           

          If ( {your expression for >2yrsold} ; "M" ; "" )

           

          Don't put the date field onto the gentleman's layout...just put the "M" field onto the gentleman's layout.

          • 2. Re: Change Date into letter
            daveealex
              

            Ninja,

             

            thank you for the quick reply. I did not make the equation myself, I used the conditional formatting from the menu. I am not even sure how the equation would be If I were to write it. If you have time to show me I would appreciate the help.

            • 3. Re: Change Date into letter
              ninja
                

              Howdy daveealex,

               

              For your (his) use, can it be as simple as:

               

              If ( Table::StartDate < ( Get(CurrentDate) - 731) ; "M" ; "")

               

              or will leap year be a big issue?

               

              If so, there's a cool (more complex) year function on the FMP knowledge base.

              • 4. Re: Change Date into letter
                daveealex
                  

                 Ninja

                 

                Thanks for all the help all is ok

                 

                daveealex

                • 5. Re: Change Date into letter
                  daveealex
                    

                   Ninja

                   

                  We had to change the way the card expire. Now the expire on jan 1 every year. I have created a field with that shows the date when they paid. I have inserted the calculation below in the conditional formating and it did not give me any errors. When i move my computer ahead to jan 2010 nothing happen. It should show a M if they have not paid, I can not get it to work. After I get this to work the next step will be to create a button that resets the field to the new paid date(I can create the button).  I hope this is clear. Thank you in advance

                  if (Table::date  ≥ (Self≥"year+1"); "M" ; "")

                   

                  Dave

                  • 6. Re: Change Date into letter
                    Sorbsbuster
                      

                    I'm not sure how your formula fits with your fields, but inside ' (Table::date  ≥ (Self≥"year+1"); "M" ; "")' you have quotation marks around part of the calculation: "year+1".  This will compare the field itself with literally the expression "year+1".  I suspect that you mean to compare it with the Field Name 'Year' plus 1.

                     

                    That would be

                     If (Table::date  ≥ (Self ≥ (year+1); "M" ; "")

                     

                    Alan.

                     

                    • 7. Re: Change Date into letter
                      daveealex
                         Thank you for the response Alan. I did not see that, but my problem still exists. I will try to explain a little better.First when i try to use Year (get(CurrentDate)) I get back 2/2/0006 I am not sure why so i have gone passed this to just using get(CurrentDate). Now the second thing is I have another field named paid in which I need to change to an M when the date hits Jan 01. I am not sure what to do. I have tried this formula in conditional formating If ( Get ( CurrentDate )  ≥  1/1/2010 ; "M" ; "") and many other variations. I would like also to have it so i dont have to modify the 01/01/2010 to 01/01/2011 and so on. Thank you again
                      • 8. Re: Change Date into letter
                        raybaudi
                          

                        Get ( CurrentDate )  ≥  1/1/2010

                         

                        is the same of saying:

                         

                        Get ( CurrentDate )  ≥  .0004975124378109

                         

                        Use, instead:

                         

                        YourDateField  ≥  Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) )

                        • 9. Re: Change Date into letter
                          daveealex
                            

                          daniele,

                           

                          i tried this and it work, but do to the fact i have passed 01/01/2009, it automatically turns and stays red(my color to show it has expired). Is there a way to reset the date and color to the normal white when they have paid for the new year? Then repeat the process