Date calculation fields are a versatile tool within Membrain that enable you to perform date-related calculations to generate new dates or determine intervals between dates. These fields can be used across Activities, Companies, Contacts, and Projects to add more depth and intelligence to your data management.
How Date Calculation Fields Work
The output of a Date Calculation Field is either a date or empty. Similar to Calculation Fields, Date Calculation Fields allow you to set up custom formulas to generate new dates based on your existing date data.
To create a Date Calculation Field, follow these steps:
- From the process editor or Custom Field management tab in System setup, create a new Date Calculation Custom Field.
- Use the Insert Property button to build your date calculation. In this article, variables referenced in formulas will be prefixed with @, for example, @CreatedDate.
Note: A date calculation field must return a date not a number otherwise the result will be invalid.
Value Types
Date Calculation Fields work primarily with date values. Here are some considerations regarding the value types:
- Dates: Dates are formatted as 'YYYY-MM-DD'. They can come from a Date Custom Field or one of Membrain's base date properties, such as Closing Date.
- Intervals: Days, months, and years can be added to dates using the following notations:
- D for days
- M for months
- Y for years
Functions
Date Calculation Fields support various functions to manipulate and calculate dates:
- DaysBetween: Returns the number of days between two dates.
Example: DaysBetween('2023-01-01', '2023-02-01') = 31
- MonthsBetween: Returns the number of months between two dates.
Example: MonthsBetween('2023-01-01', '2023-04-01') = 3
- YearsBetween: Returns the number of years between two dates.
Example: YearsBetween('2023-01-01', '2025-01-01') = 2
- AsDays/AsMonths/AsYears: Converts a field or value into days, months, or years.
Example: CreatedDate + AsDays(5)
- Min
Takes any number of date arguments and returns the earliest date.
- Max
Takes any number of date arguments and returns the latest date.
- Sum
Sum takes any number of arguments, either a number, or a referenced numerical property and returns the sum of those numbers. Example: Sum(1,5,3) = 9. Sum is thus exactly the same as 1+5+3.
- Average
Average gives you the average out of a series of numbers. Example: Average(1,2,3) = 2. You can also access properties, if the property is empty, it will be excluded from the average. Example: Average(1,@Empty,3) = 2.
- Default
The default function can be used in cases where the value from a property may be empty, to resolve a default value. Example: Default(@Property, 5). In this case the value will be 5, if the Property is empty, otherwise it will use the value of the property.
- Round
Rounds a number either up or down depending on its value. Example: Round(0.3) = 0. Round(0.6) = 1
- Floor
Rounds a number down. Example: Floor(0.9) = 0. Floor(0.1) = 0
- Ceil
Rounds a number up: Example: Ceil(0.1) =1. Ceil(0.8) = 1
Note: Average, Round, Sum, Floor, and Ceil will not have a meaningful effect on dates but will work on numbers within date calculations
Examples
Adding Days to a Date
If you want to add a specific number of days to a date, you can use the following setup.
Using Functions for Intervals
You can use functions to calculate intervals between dates and add them to another date. For instance:
@CreatedDate + DaysBetween(@CreatedDate, @ChangedDate)
Nesting Functions
Functions can be freely nested to perform more complex calculations. For instance:
@CreatedDate + AsMonths(5) + DaysBetween(@CreatedDate, @ChangedDate)
Conclusion
Date Calculation Fields in Membrain provide a powerful way to handle date data, allowing you to perform complex date manipulations and generate new dates based on existing data. By understanding and utilizing the various functions and value types, you can enhance your data analysis and management capabilities within the platform.
