3 Replies Latest reply on Aug 8, 2013 8:27 PM by ziceg

    How to calculate and End Date

    ziceg

      I have the following fields:

       

      Start Date [Date Type]

      Duration [Text Type] (which for the moment is text as it could potentially be 3 months, 6 months, 1 year, 2 years... [currently empty so can change])

      End [Calculation Type]

       

      How do I make a calculation so that in the "End" Field it tells me when the contract will end?

       

      I know the fault is in the Duration field, but don't know how I can set it up so that it will work and make sense and the calculation to work.

       

      Thanks in advance for any help

        • 1. Re: How to calculate and End Date
          doughemi

          separate the duration field into two fields, duration and duration units.  End date calculation would then be

           

          Case(

          duration units = "months"; Date(Month(Start Date) + duration; Day(Start Date); Year(Start Date));

          duration units = "years"; Date(Month(Start Date); Day(Start Date); Year(Start Date) + duration)

          // etc if more duration units are anticipated

          )

          • 2. Re: How to calculate and End Date
            jbante

            Separate the duration field into two fields: duration [number type, representing the number of days], and durationText [calculated text based on duration to figure months and years]. Then endDate is simply startDate + duration (calculation result is type Date).

            • 3. Re: How to calculate and End Date
              ziceg

              Thanks for the help.  I ended up creating a new table to store both the options and the number of days to make the calculation easier, and then just creating a value list with the items and using those values in second column to do the calculation.