When working with business processes in Creatio, the most common element we run into is the “value” that needs to be assigned so it does what it needs to do.

In some situations, that value needs to be a more complex formula with a few steps of logic to produce the expected output.

When working with dates in particular, formulas can become more complicated, as adding days to a date requires calling special date-related functions to properly calculate a Date/Time value.

Creatio’s formula element provides a nice way to supply a value or a formula. Here is a simple example of the Opportunity Close date being assigned to a parameter of the process. This parameter can then be used where needed.

The formula value can be a simple value that is returned from another element, or it could be a formula that performs conversions.

We might want to just extract the Year value, or possibly find a date that is a few months past the date we are working with. There are many common date conversions like this that we run into, so we are creating a simple business process with an auto-generated page to show how different date conversions can be performed.

We will start with creating a business process in Creatio that reads data from an Opportunity record and performs some calculations on the opportunity Closed On date.

First, we’ll set up the process with a read element, and then we’ll set up an auto-generated page that can both calculate and display the result on the page.

Configure the “Read some data” element to read an opportunity record that has the Closed On date populated.

Next, set up a couple of process parameters to hold a Start and End date. The Start date will be set to the Closed On date of the retrieved opportunity record. The End Date will be set to two and a half days ahead of the Start Date.

Notice how the End Date has a date conversion formula of 2.5 days ahead of the Start Date. This is just an arbitrary value we picked so that we can use these start & end date calculations.

Finally, we need to configure the “Page Items” in the auto-generated page called “Show Date Calculations”. We will also set up a couple of date fields that show the Start and End date values at the top of the page so it is clear which date values the calculations are based on.

Now it’s time to calculate a bunch of date-related values!           

1. How do I get a date that is 3 days ahead of my Start Date?
   1. Use the AddDays function and specify the number of days as 3.
   1. Formula: [#StartDate#].AddDays(3)
2. How do I get the name of the weekday that is 3 days ahead of my Start Date?
   1. Use the AddDays function and then also add the DayOfWeek function to get the day. Then append the ToString to show it as text.
   1. Formula: [#StartDate#].AddDays(3).DayOfWeek.ToString()
3. How do I get the month name of the date that is 3 months ahead of my Start Date?
   1. Use the AddMonths function first to get the desired date while indicating the number of desired months as a parameter
   1. Use the ToString function with a parameter of “MMMM” to get the full month name. Using “MMMM” will produce just an abbreviated month name
   1. Formula: [#StartDate#].AddMonths(3).ToString("MMMM")
4. How do I get the Year number of the date that is one year ahead of my Start Date?
   1. Use the AddYears function to add the desired number of years while indicating the number of years as a parameter
   1. Formula: [#StartDate#].AddYears(1).Year
5. How do I add 4 hours to my date?
   1. Use the AddHours function and specify how many hours need to be added.
   1. Formula: [#StartDate#].AddHours(4)
   1. Note that you can also use AddMinutes and AddSeconds depending on how granular the calculation needs to be.
6. How do I calculate the total days between two dates?
   1. Use the TotalDays function on the difference of the End and Start dates
   1. Formula: ([#EndDate#]-[#StartDate#]).TotalDays
7. How do I calculate the total hours between two dates?
   1. Use the TotalHours on the difference of the Start and End dates
   1. Formula: ([#EndDate#]-[#StartDate#]).TotalHours
   1. Note that you can also use TotalMinutes and TotalSeconds if needed.
8. How do I calculate the calendar quarter for a date?
   1. Use the formula (Month/3) to find the quarter number, then use the ToString function to convert to text and append with “Q” so the result looks like “Q2”
   1. Formula: "Q"+ RoundUp([#StartDate#].Month/3).ToString()
9. How do I calculate the fiscal quarter for a date?
   1. Use a formula like above with slight modifications to retrieve the Fiscal quarter number for the selected Start date. Since the start date value for this example is in May, this date would correspond to the first fiscal quarter as the fiscal year ends in April.
   1. Formula: "Q"+((int)([#StartDate#].AddMonths(-3).Month+2)/3).ToString()

Here’s how all the above formulas look when they’re properly set up:

Now, let’s take a look at the output. Just hit the run button on this process and an auto-generated page should show in the notifications area with all the calculation results:

Hopefully these examples provide a good basis to perform any date based calculations you need in Creatio. Feel free to reuse these to reduce your codebase as much as possible.