8 Replies Latest reply on Aug 6, 2013 9:15 AM by philmodjunk

    If function involving date ranges

    JenC

      Title

      If function involving date ranges

      Post

           In my first ever Filemaker Pro DB, I have a number of fields including Date and Financial Year. As I am importing a large number of records, I wanted the Financial Year field to automatically fill, depending on the record date.

           To achieve this, I decided to use the If function, specifically:

           If (Date < 1/7/2013 ; " July '12 - June '13" ; "July '13 - June '14" )

           However, all records, regardless of date, are returning "July '13 - June '14". I suspect that my use of "< 1/7/2013" to represent dates before 1 July 2013 is the problem.

           Can anyone help?

        • 1. Re: If function involving date ranges
          SteveMartino

               Couple of things for starters......make sure you date field is set properly in the inspector.   Unless its a typo, you have 1/7/2013 to represent July 1, 2013, where it usually represents Jan 7, 2013.  But that's probably not your main problem.

               It may be easier to use Replace Field Contents, but can you show a screenshot of the calculation window?  I suspect in place of the word  Date, it should read 'yourTable::Date' without the quotes, unless your just abbreviating in your post. 

          • 2. Re: If function involving date ranges
            JenC

                 Thanks for your reply, Steve.

                 I'm sending this question from Australia where 1/7/2013 represents 1 July 2013 - we do things a bit differently down here!

                 The Replace Field Contents option wouldn't solve my problem as the contents of the Financial Year (which I believe elsewhere is referred to as the Fiscal Year) will vary, depending on the record date. If everything was working as I was hoping it would, a record for the 29 July 2013 would return a result under the Financial Year of July '13 - June '14, whereas an entry for the 1 June 2013 would result in a Financial Year entry of July '12 - June '13.

                 I have uploaded an image of the screenshot of the calculation window (I am not sure if I have done this correctly - my first time!)  Hopefully this will help you understand what I am trying to achieve. As a beginner to Filemaker Pro, I am not even sure if I am describing my problem using the correct language.

                  

            • 3. Re: If function involving date ranges
              SteveMartino

                    No Screenshot.  Make sure your screenshot is in the proper format (GIF, JPG or PNG).  When you are in layout mode and have the inspector on the Data Tab, at the bottom, does the format selected for date match the format you are using? 

                    

              • 4. Re: If function involving date ranges
                philmodjunk

                     Your expression has a major syntax error. In this expression:

                     If (Date < 1/7/2013 ; " July '12 - June '13" ; "July '13 - June '14" )

                     FileMaker evaluates 1/7/2013 as 1 divided by 7 divided by 2013. It does not evaluated it as a date. Use one of these expressions:

                     If (Date < GetAsDate ( "1/7/2013" ) ; " July '12 - June '13" ; "July '13 - June '14" )

                     or

                     If (Date < Date ( 7 ; 1 ; 2013 ) ; " July '12 - June '13" ; "July '13 - June '14" )

                     I'm guessing that your dates are formatted in DDMMYYYY format. the Date function requires parameters in this order: Date ( Month ; day ; year )

                • 5. Re: If function involving date ranges
                  JenC

                       I realised I didn't have the right syntax for the date so I used your second suggested option 'If (Date < Date ( 7 ; 1 ; 2013 ) ; " July '12 - June '13" ; "July '13 - June '14" )' and it worked beautifully.

                  I also realize that even though tI have set up the date under an Australian date format (ie dd/mm/yyyy) the expression only worked correctly when I used the American date format in the formula (ie mm/dd/yyyy)

                  Thanks for your help.

                  • 6. Re: If function involving date ranges
                    philmodjunk

                         That's because it's not a formula. It's a call to a function and as is the case with any defined function, the parameters must be listed in the order that the function was defined to use.

                    • 7. Re: If function involving date ranges
                      JenC

                           I hadn't saved the screenshot as a JPEG or GIF but I have now worked out how to do this.

                           My problem has now been solved when I used the expression 'If (Date < Date ( 7 ; 1 ; 2013 ) ; " July '12 - June '13" ; "July '13 - June '14" )'.

                      But you were right about the date format being used. In the inspector I have set the date using the Australian format (DD/MM/YYYY) but the expression only works when I use the American date format (MM/DD/YYYY). My other problem was with my incorrect use of '/' in the date (namely 1/7/2013) as part of the formula.

                      This has been a real learning experience! Thanks. 

                      • 8. Re: If function involving date ranges
                        philmodjunk

                             Please note that there are two different settings that affect MMDDYYYY vs. DDMMYYYY formats. The Data format setting that you made controls how the data is displayed once you exit the field, but there's a locality setting for your computer (See file options) that FileMaker can access that determines whether a date typed into a date field like this:

                             2/1/2013

                             Enters a date for February first or January 2nd.

                             The same settings control what value is returned by: GetAsDate ( "2/1/2013" )