Date Functions

Logical Functions

• Basic comparison operators: < > <= >= <>
• IF([expression ], TrueResult , FalseResult ), evaluates the logic of the expressions and returns TrueResult if the expression is true and FalseResult if the expression is false.
• AND([expression1 ], [expression2 ]), returns TRUE if expression1 and expression2 are both true, otherwise returns FALSE.
• OR([expression1 ], [expressions2 ]), returns TRUE if either expression1 or expression2 is true; returns FALSE if both expressions are false.
• ISBLANK([field ]), returns TRUE if the field is blank, otherwise returns FALSE.
• Nested IF, you may nest IF functions within other IF functions. You can nest up to 7 levels.

Date Functions

• Basic arithmetic operators: + -
• YEAR([date ]), returns the year in the date , 0000-9999.
  Example: YEAR([datefield]) where datefield points to 11/08/18 returns 2018.
• MONTH([date ]), returns the number of the month in the date , 1-12.
  Example: MONTH([datefield]) where datefield points to 9/8/18 returns 9.
• DAY([date ]), returns the number of the day of the month in the date , 1-31.
  Example: DAY([datefield]) where datefield points to 11/08/18 returns 8.
• DAYSCOUNT([Start Date Field ], [End Date Field ], [Sub-Start Date Field ], [Sub-End Date Field ], "MMM-YYYY"), returns the count of days from a Sub-Start Date to the specified "MMM-YYYY".
  Example: DAYSCOUNT([Planning Start], [Planning End], [Contract Start], [Contract End], "Nov-2018")
• See Example: Calculating DaysCount and DaysFactor for a Contract within a Planning Cycle
• DAYSFACTOR([Start Date Field ], [End Date Field ], [Sub-Start Date Field ], [Sub-End Date Field ], "MMM-YYYY"), factor based on the count of days from a Sub-Start Date to the specified "MMM-YYYY" divided by the number of days in that month.

Example: DAYSFACTOR([Planning Start], [Planning End], [Contract Start], [Contract End], "Nov-2018")

See Example: Calculating DaysCount and DaysFactor for a Contract within a Planning Cycle

• DAYS([datefield2 ], [datefield1 ]), returns the number of days between datefield1 and datefield2 . For a positive value, datefield2 should be after datefield1.
• WEEKNUM([date ]), returns the count of weeks from the beginning of the year to the date specified.

Example: WeekNum("2/1/18") returns 5 because Feb 1 falls in the middle of the 5th week of 2018.

• DATE(mm, dd, yyyy ), returns the date in the format MM/DD/YYYY.
• EOMONTH([start_date ], number of months ), returns the date for the last day of the month that is indicated by the number of months before (negative value) or after (positive value) the start_date . The argument start_date must be specified by using a date field, the DATE function, or the result of other formulas. The argument number of months must be an integer; any non-integer value is truncated. EOMONTH calculates project end dates that fall on the last day of the month. Both arguments are required.

Example: EOMONTH([Start_Date], -3) where Start_Date is May 23, 2019 returns Feb 28, 2019.

See Example: Using EOMonth, EDate, Min, Max, and Text Functions

• EDATE([start_date ], number of months ), returns the date that is indicated by the number of months before (negative value) or after (positive value) the start_date . The argument start_date must be specified by using a date field, the DATE function, or the result of other formulas. The argument number of months must be an integer; any non-integer value is truncated. EDATE calculates project end dates that fall on the same day of the month as the project start date. Both arguments are required.

Example: EDATE([Start_Date], -3) where Start_Date is May 23, 2019 returns Feb 23, 2019.

See Example: Using EOMonth, EDate, Min, Max, and Text Functions 

• SUM([datefield ], n), returns the date that is indicated by the number of days, n , added to the datefield . To add to the datefield , n should be a positive number. To subtract from the datefield , n should be a negative number.

See Example: Using Date Arithmetic Functions

Example: Calculating DAYSCOUNT and DAYSFACTOR for a Contract within a Planning Cycle

DAYSCOUNT calculates the number of days that a contract is in progress in a particular month. DAYSFACTOR calculates the percentage of time that a contract is in progress in a particular month. In the following example, assume the following values:

• Contract Start is 03/15/2023 and Contract End is 05/31/2023.
• Planning Start is 01/01/2023 and Planning End is 12/31/2023.
• DAYSCOUNT for Feb-2023 is 0. DAYSCOUNT for Mar-2023 is 17. DAYSCOUNT for Apr-2023 is 30. DAYSCOUNT for May-2023 is 31.
• DAYSFACTOR for Feb-2023 is 0.0. DAYSFACTOR for Mar-2023 is 0.548. DAYSFACTOR for Apr-2023 is 1.0. DAYSFACTOR for May-2023 is 1.0.

Example: Using Date Arithmetic Functions

The following External Source Model uses five input fields and three formulas.

Five fields are inputted:

• Name
• Work Start Date
• Duration in Days
• Bid Amount
• Status

The other three fields are calculated:

• RFP Start Date uses date arithmetic to subtract 30 days from the Work Start Date. This date provides a timeframe for the Request for Proposal to be written.
• Midpoint Date uses date arithmetic to calculate the midpoint date of the project. It uses the input field Duration in Days, divides it in half, and adds that number to the Work Start Date.
• Projected End Date uses the SUM function to demonstrate how to add the input field Duration in Days to the input field Work Start Date.

The external data source contains the following fields.

After loading this data into the External Source Model, the three formula fields are calculated. You can see that the Projected End Date is the Work Start Date plus the Duration. The Midpoint Date is halfway between the Work Start Date and the Projected End Date. The RFP Start Date is 30 days before the Work Start Date.

Example: Converting Transaction Date MM/DD/YY to Separate Fields: Month MM and Year YYYY

In the example below, the source data contains 6 columns (rows 5-10). The Master Model requires that the transaction date (Time) be broken out into separate Month and Year dimensions, so expressions are added that render these fields. Since the expression MONTH[Time] renders as a month number without a leading zero (such as month 1, 2, 3, 10, 11, 12), a constant "0" is concatenated ahead of the month number (such as 01, 02, 03, 010, 011, 012). Then final Month ID is calculated as the right two characters of that string.