3 Replies Latest reply on Jun 8, 2015 11:24 AM by philmodjunk

    Using dates in If statements



      Using dates in If statements



      I haven't worked too much with dates and what I need to do is have a calculation field return zero if the date of one field is less than a specified date. What I am trying to do is set a field to 0 if it is x years before this current year.



      If( Assets::DateIssued > 01/01/2010 ; 0 ; 1 )

      Obviously that's incorrect formatting, but I hope I'm relatively clear with what I'm asking about.


      I tried a workaround to make a field with todays date named Assets::DateToday with an auto-calc of Get(CurrentDate). Then I made the field in question a calc field with the following:

      If( Year(Assets::DateToday) - Year (Assets::DatePurchased) > Assets::YearSpread ; 0 ; 1 )


      The above is not functioning correctly even though I believe it should work. Help?

        • 1. Re: Using dates in If statements

          Filemaker perceives: 01/01/2010 as the number 1 divided by 10 divided by 2010. The / character is the division operator. To get this constant to evaluate as a date, you need to use either getasDate or the date function:

          date ( 1 ;1 ; 2010 )----> parameters are month ; day ; year

          or getasdate ( "1 / 1 / 2010" )  ----> can evaluate differently depending on whether locality settings specifiy ddmmyyyy or mmddyyyy formats for data entry.

          Can't tell why your work around didn't work as I don't know what kind of field is YearSpread nor what kind of value might be entered into it. The most obvious thing to check is to make sure that it is of type number and not type text.

          Other issues: It appears that you need a result of 1 (True ) or 0 (False). You don't need to use the If function to get that result.

          If( Assets::DateIssued > getasdate ( "01/01/2010" ) ; 0 ; 1 )


          Assets::DateIssued > getasdate ( "01/01/2010" )

          will produce exactly the same results.

          • 2. Re: Using dates in If statements

            I figured it out but this info is useful for other things I'm doing as well! I wasn't looking for a True False, it was just arbitrary numbers. The solution that worked is I eliminated the DateToday field and wrote it in:

            If(Year(Get(CurrentDate)- Year(Assets::DatePurchased) > Assets::YearSpread; 0; 1 )

            Posting the above calc if anyone else runs into something similair.

            Thanks PhilModJunk!

            • 3. Re: Using dates in If statements

              Year(Get(CurrentDate)- Year(Assets::DatePurchased) > Assets::YearSpread

              Will produce exactly the same result.

              If this is in a calculation field, this should be set up as an unstored calculation or it will fail to update as the current date changes.