Forum Discussion

JaclynSchlipp's avatar
JaclynSchlipp
Qrew Member
5 years ago

Calculate contract length

I am trying to calculate the length of a contract using the start date and end date of the contract.

I have a field for contract length (years) and a field for contract length (months). We do have contracts that are less than one year hence the months field.

The problem I am encountering is the contracts are based on an academic year (08/01 - 05/31) and I do not know how to build a formula to capture a specialized date range. 

We have 2 types of contracts: 

Example: contract 1: 08/01/19 - 5/31/23 = 4 year length academic contract

Example: contract 2: 08/01/19 - 07/31/23 = 4 year length annual contract (non-academic) 

I also cannot figure out how to calculate only months for those contracts that are less than 1-year.


------------------------------
Jaclyn Schlipp
------------------------------
Formulas and functions
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    5 years ago
    I'm not entirely sure I understand your first question.  Are you looking to calculate the contract for years and months for the 4 year contracts?

    For your second question:

    Month(End Date) - Month(Start Date)

    ------------------------------
    Paul Peterson
    ------------------------------
    • JaclynSchlipp's avatar
      JaclynSchlipp
      Qrew Member
      5 years ago

      Hi Paul,

      I need to calculate lengths for all contracts.  The 4-year contract was just an example. 

      Scenerio 1:
      Contracts have an appointment term based off of an academic year (Aug - May). There can be 1 -6 year terms per faculty. We are manually entering an appointment start date (08/01) and appointment end date (5/31) into QB and would like to have a field that calculates the lengths per dates of appointment. 

      Scenerio 2:
      Contracts have an appointment term based off a 12-month year (Aug - Jul ; Jul - Jun; Oct - Sep; etc). 

      Scenerio 3:
      Contracts have an appointment term of less than a year (Aug - Dec; May - July; Sep - Jul; etc).

      Thanks!

      Jackie

       



      ------------------------------
      Jaclyn Schlipp
      ------------------------------
      • PaulPeterson1's avatar
        PaulPeterson1
        Qrew Assistant Captain
        5 years ago
        Let's see if I have a better grasp now.

        You need three calculations.  

        One is based on a school year and you need to calculate the number of years remaining.  The next one is a multi-year contract based on a rolling calendar.  And the last is set for a given number of months.  Is that how you want the calculations?

        ------------------------------
        Paul Peterson
        ------------------------------