2 Replies Latest reply on May 29, 2011 6:01 PM by LaRetta_1

    If formula problems



      If formula problems


      I cannot seem to get an If formula to work. Any help would be greatly appreciated.

      I created a stock transaction database. To do that, you need to know when you bought and sold the stock. I also want to create field that will return an number that represents the time in years that that stock has been held. I want this field to return the hold period even if the stock has not been sold and is still owned.  

      To do this, I felt that an IF formula should do the trick, but I cannot seem to get it to work. Here is what I put together;

      If(Sell Date = "";(// - Purchase Date)/365;Sell Date -Purchase Date)/365 )

      The Sell Date is the date the stock is sold, but if the stock has not been sold yet this field will be empty. For this reason, I used the Sell Date field as the test in the IF formula.

      Sell Date = "" This is the test that basically if Sell Date is empty, then....... Is the "" the right way to represent an empty field?

      The "//" I believe is the return for the current date. So if there is nothing in the Sell Date field, then the value will be the current date - the Purchase Date.

      But when I put this in FM, I get the return, "A number, text constant, field name or "(" is expected here."

      What am I doing wrong? Any suggestions would be greatly appreciated. 

      I have FMPro Advanced 11.0 v3OS - Windows Vista on desktop, Windows 7 on laptop

        • 1. Re: If formula problems

          I have not the foggiest idea what answer your calculation should return so I am not sure if this one works either, but here is how I would set it up:

          If ( IsEmpty ( SellDate ) ; (Get ( CurrentDate ) - PurchaseDate) / 365 ; (SellDate - PurchaseDate) / 365 )

          If not then a little more info and we'll try again. BTW the double slashes in a calculation are for commenting out the remainder of a line, ie, a way to leave notes to yourself. The calculation engine ignores that line.

          • 2. Re: If formula problems

            I would assume the logic is this:

            If there is a Sale Date, subtract Purchase Date from Sale Date.  If no Sale Date, subtract Purchase Date from current date.  Return results if only greater than (or equal to) a year.  The problem with using 365 is that it will not always be accurate because of leap year.  Even using 365.25 etc will still be off depending upon the span of years involved.

            The truly safe way is to test for year difference and then see if the current date's month and day is before the Purchase Date's month and day and if so, subtract a year because it has not yet hit the anniversary.  You might try this calculatioin (result is date) and be sure to set the calculation to 'unstored' in the Options or it won't update as the current date changes:

            Let ( [
            now = Get ( CurrentDate ) ;
            sell = Case ( not Sale Date ; now  ; Sale Date )
            ]  ;
            Year ( sell ) - Year ( Purchase Date )  - ( ( Month ( sell ) + Day ( sell ) / 100 ) < ( Month ( Purchase Date ) + Day ( Purchase Date ) / 100 ) )

             Double front-slash means the current date when searching or on layouts (so I assume you meant to use it as the current date) but, as Bumper nicely points out, it means a comment when used within a calculation (but only to comment out the specific line it is on). 

            Anyway, let us know if we have misunderstood your need.