Add a Column Based on a Formula Calculation

If you choose Formula as the data entry method, you can create a numeric, a "date add," or a "date difference" type of formula to automatically calculate the values for the fields in the column.

The numeric formula option is only available if the data source you specify has been built on the SYS Numeric Logical Datasource data definition.

A "data add" formula takes the value from a date field and adds the value of another field to it. This option is only available if the data source you specify has been built on the SYS Date Logical Datasource data definition.

A "date difference" formula takes the value from a date or date field and subtracts it from the value in another date or date field. This option is only available if the data source you specify has been built on the SYS Numeric Logical Datasource data definition.

To create a numeric formula for a column

  1. In the Data Source field, choose the field that contains the value you want to include in this column.
  2. In the Entry Method field, choose Formula and click the Create button. The Formula Creation window opens.
  3. In the Data Type field, specify where the field comes from—a planning item, or the planning sheet. The Formula Creation window opens, showing a list of the fields from the data type you selected.
  4. Create the formula using the field values in this data source, or field values and numeric operators, such as add, subtract, or multiply.
  5. Click OK.

To create a "date add" formula for a column

  1. In the Data Source field, choose the field on the sheet that should show the value you are calculating here.
  2. In the Entry Method field, click Formula, then click the Create button. The Create Formula window opens.
  3. In the Date field, select the field to which you want to add another value as follows.
    1. Click the Select button. The Date Element Picker window opens.
    2. In the Data Type field, specify where the date field you want to add to comes from—a planning item, or the planning sheet. Unifier displays a list of the fields on the planning item form, or the planning sheet.
    3. Select the field and click OK.
  4. In the Add field, select the field that contains the value you want to add to the field.
  5. Specify whether the calculation should be based on calendar days or work days.
  6. Click OK. Unifier displays the formula on the Column Properties window.

To create a "date difference" formula for a column

  1. In the Data Source field, choose the field on the sheet that should show the value you are calculating here.
  2. In the Entry Method field, click Formula and select Date Difference from the list.
  3. Click the Create button. The Date Difference window opens.

    The first selected date will be subtracted from the second selected date to produce the date difference.

  4. For the Earlier and Later Dates, you can use either the last date the Planning Sheet was updated, or you can choose another field from the sheet or a planning item to supply the date value.
  5. In the Calculation based on field, specify whether the calculation should be based on calendar days or workdays.
  6. If you want the value in include fractions of days, select the Show Partial Days check box.
  7. Click OK.

Last Published Monday, August 28, 2023