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

    Change Date into letter



      Change Date into letter


      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



        • 1. Re: Change Date into letter

          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



            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

              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



                Thanks for all the help all is ok



                • 5. Re: Change Date into letter



                  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" ; "")



                  • 6. Re: Change Date into letter

                    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" ; "")




                    • 7. Re: Change Date into letter
                         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

                        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



                          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