10 Replies Latest reply on Nov 3, 2010 12:49 PM by rezap

    Date Calculations Using Operators

    rezap

      Title

      Date Calculations Using Operators

      Post

      I am attempting to make a calculation using one date field to calculate another date field. 

      EndDate1 

      StartDate2

      Once EndDate1 is entered into the field, I want to auto-add a day to StartDate2.

      EXAMPLE:

       If the user enters EndDate1 as 11/2/2010, then StartDate2 will automatically generate 11/3/2010 

      I am using the Auto Enter Calculation- is this the right route to got to make this work?

      Manage Database>Fields>StartDate2

      I use the option to AutoEnter a calculation based on EndDate1 to make the plus 1 calculation, but I am having a problem with the formula.  Can someone please help with the formula using these operators or direct me on how to make this work? Thanks in advance. 

      Screen_shot_2010-11-03_at_1.19.12_PM.png

        • 1. Re: Date Calculations Using Operators
          ryan

          If End Date1 is a date field, then make End Date2 a Calculation field, with result being a Date.

          then enter a simple calculation as:

          End Date1 + 1 

          • 2. Re: Date Calculations Using Operators
            philmodjunk

            If you want to be able to change startdate 2 to a different date if needed, then the auto-enter date option will work. DateFields simply store whole numbers counting days from an early arbitrary date.

             So to get a date one day later, you just add one to your date.

            EndDate1 + 1 will return a date one day after the date in EndDate1.

            Note, there are limitations in how an auto-entered calculation updates. If you want future changes to the value in EndDate1 to automatically change the date in StartDate2, this approach will not work unless you clear the "Do not replace existing value..." option and then only if StartDate2 and EndDate1 are defined in the same record.

            • 3. Re: Date Calculations Using Operators
              rezap

              Both EndDate1 and StartDate2 are in the same record and I unchecked "Do not replace existing value" but it still does not run the calculation.  Do you know what could be the issue here?

              • 4. Re: Date Calculations Using Operators
                philmodjunk

                Try this with a new record or change the value in EndDate1 to cause FileMaker to re-evaluate the calculation. Auto-entered calculations won't compute values for pre-existing records unless you do something to each such record to trigger them to re-evaluate. All new records should compute and auto-enter values correctly.

                A one time fix for this is to copy this calculation expression to the clip board, then use Replace field contents with the calculation option to update all your existing records. You can paste your copied expression into the specify calculation dialog so that exactly the same calculation as your auto-enter field has is used to update the existing records.

                • 5. Re: Date Calculations Using Operators
                  rezap

                  Tried the new record and the calculation will still not perform.  This database is still in the design phase and this is my first database I have built using filemaker, but I am fairly certain that I have set up everything correctly with the tables, fields, relationships, allowing data to be modified through existing relationships.  Could it be a design issue or is there something else missing?

                  • 6. Re: Date Calculations Using Operators
                    philmodjunk

                    It's hard to say from here. We can't see your computer screen while you try to set this up. Wink

                    Are EndDAte1 and StartDate2 both of type Date and not type text?

                    If you don't need to allow people to modify StartDate2, just use a field of type calculation instead of an auto-entered calculation as suggested by rdowler.

                    • 7. Re: Date Calculations Using Operators
                      philmodjunk

                      Here's a demo file you can download and compare to yours:  http://www.4shared.com/file/6xBikXIm/AutoEnterDateCalc.html

                      • 8. Re: Date Calculations Using Operators
                        rezap

                        Wow! That explains a lot :) 

                        I'm assuming from this example that both fields  have to be in the same table in order to make the calculation work....I assumed that EndDate and StartDate could be different tables

                        • 9. Re: Date Calculations Using Operators
                          philmodjunk

                          I did say that they had to be in the same record--which requires that they be in the same table Wink

                          It will work with separate, related tables as long as the relationship is valid and you don't need updates to EndDate to automatically update the StartDate field.

                          A calculation field may work better here as it will update automatically when you edit the EndDate in a related record.

                          • 10. Re: Date Calculations Using Operators
                            rezap

                            That worked like a charm.  The forums here are great!  Thanks for the help!