You can create formula columns on a schedule sheet or template. Formulas can calculate differences between date columns, or add / subtract numeric columns / values from dates. This is useful to calculate durations or to calculate one date from another date by adding/subtracting days.
You can define formulas of the following types:
- Numeric: Build a numeric formula. This choice is available if the data source is SYS Numeric Logical.
- Date Difference: Formula is by default Later Date – Earlier Date. This choice is available if the datasource is SYS Numeric Logical.
- Date Add: Add a numeric value to a date. This is available if the datasource is SYS Date Logical.
SYS Numeric Logical and SYS Date Logical are data definitions form which data elements can be created in uDesigner and used on schedule attribute forms.
How to tell which data source to use in building a formula
The ability to build formulas is dependent on the data definitions used to create the data elements on the schedule attribute form. You can navigate to Administration Mode > Data Structure > Data Elements to see which data elements are based on the above data definitions (click the column heading in the Data Elements log to sort by data definition. Alternatively, you can choose a Data Source in the drop-down list, then, with the Data Source field still active, press your keyboard’s up and down arrow keys to scroll through the data source options. When one of the above data definitions is reached, the Formula radio button becomes active.
To create a numeric formula
Choose Numeric and click Create. Activity data type displays data elements that are defined on the detail form along with "SYS Numeric Datasource" and "SYS Numeric Logical Datasource".
To create a date difference formula
- Choose Date Difference and click Create.
- Enter Earlier Date and Later Date:
- Click Select. The Data Element picker opens.
- Choose a data element. Options are data elements from schedule sheet detail form or columns defined on schedule sheet. Only date type (Timestamp) data elements are displayed.
- Choose Calculation based on Calendar days (non-working days will be counted) or Work days (non-working days will be ignored). Work days are defined in the company calendar.
- Click OK.
To create data add formula
- Choose Data Add and click Create.
- Choose a data element for Date and Add (click Select to open the data element picker). Data elements on the schedule sheet detail form and schedule sheet columns are available.
- For Date, only date type (Timestamp) of data elements are displayed.
- For Add, only numeric (integer, float) type of data elements or columns are displayed in the picker list. This includes Total and Average columns.
- Choose Calculation based on Calendar days (non-working days will be counted) or Work days (non-working days will be ignored). Work days are defined in the company calendar.
- Click OK.