Date Functions
In the Calculation Editor pane, you can do one of the following to display a list of date functions and constants that you can insert in the equation:
• Click Date Functions.
• Right-click in the field where you enter the equation and select Insert Date Function.
In the calculation, any date function that you insert appears in red. You must then insert any parameters required for the date function within the parentheses. After you place the mouse cursor within the parenthesis, you can type the parameter values to use in the appropriate order. In functions where time is specified, you must use military time to indicate a PM time.
The following table describes the date and constant functions.
Function | Description |
|---|---|
DaysBetween( StartDate, EndDate, EndOption, OptionalValue ) | Allows you to return the number of days between two date fields (EndDate − StartDate). If EndDate is blank, the operation returns 0 − StartDate, which results in a large, useless negative number. Therefore, EndOption allows you to specify the behavior if EndDate is blank. For parameter descriptions, see DaysBetween Function Parameters. |
Date( Year, Month, Day ) | Allows you to specify a date in the equation. The order must always be from the biggest unit to the smallest. Thus, it must be Year, Month, and then Day. |
Time( Hour, Minute, Second ) | Allows you to specify a time in the equation. The order must always be from the biggest unit to the smallest. Thus, it must be Hour, Minute, and then Second. |
DateTime( Year, Month, Day, Hour, Minute, Second ) | Allows you specify a date and time in the equation. The order must always be from the biggest unit to the smallest. Thus, it must be Year, Month, Day, Hour, Minute, and then Second. |
CurrentDate() | Allows you to return the date on which the calculation is performed. The time defaults to 12:00:00 AM. |
CurrentDateTime() | Allows you to return the date and time at which the calculation is performed. |
OPTIONAL_RESULT | Allows you to return the OptionalValue as the result of the DaysBetween function when the EndDate is blank or zero. For parameter descriptions, see DaysBetween Function Parameters. |
OPTIONAL_ENDDATE | Allows you return the difference between the OptionalValue and the StartDate as the result of the DaysBetween function when the EndDate is blank or zero. For parameter descriptions, see DaysBetween Function Parameters. |
BLANK | Allows you return a constant of -10000. When this value is used as a result, a blank is stored for the field. An example follows: Date1 > 0 ? Date1 + 7 : BLANK If Date1 is greater than 0, then the result is Date1 + 7 days; else, the resulting field is blank. The If/Then/Else construction used in the above example is explained in Constructing the Equation for a Conditional Calculation. |
AddDays( DateTime, Days) | Allows you to add days in the equation. |
AddHours( DateTime, Hours) | Allows you to add hours in the equation. |
AddMinutes( DateTime, Minutes) | Allows you to add minutes in the equation. |
For information about how dates are converted to numeric values so that mathematical operations can be performed, see Date Conversions and Storage.