Calculation field custom equation help

[TGibs02]

Hello,

Hoping I can get some help with a formula or another way to resolve my scenario.

The scenario

When a Company becomes a 'Client' two things happen

1. We set a 'Client as of Date'

2. They are set to a type of New Business.

30 days after the Client as of Date the type is updated to Existing Business.

We are currently managing this change via a Delay in a Workflow, however I am keen to see if I can get rid of my delay and calculate the 30 days a different way.

My plan is to create a property that count downs from 30 based on the client as of date and when the property reaches 0 it triggers a WF to set the type to Existing Business. According to Open AI I can create a Calcualtion field with a custom equation that looks like this:

30 - DATEDIFF(NOW(), client_as_of_date, "days")

However when I enter that formula into the equation box I am getting errors:

Any help would be much appreciated with the formula, or is there another way to do this?

Cheers,

T

[franksteiner79 · Accepted solution]

Hi @TGibs02 

I feel your pain, I struggled with this until someone pointed me to the "add_time" function, see example below:

You do the following:

1. Property field type = calculation & custom equation
2. Output date
3. The use the "add_time" function (pick your "start" property, how much you add, and of what time format)

So in your case this should look something like: add_time('Client as of Date',30,"days").

Give that a try.

Frank

[karstenkoehler · Accepted solution]

Hi @TGibs02,

I would approach this differently entirely.

If your goal is to trigger a workflow when the 'Client as of date' is 30 days in the past, why not simply create a workflow (without a delay) that enrolls companies when 'Client as of date is more than 30 days ago', then sets the type to existing business?

Sure, you can use a calculation property here – but then you have to assets (calculation + workflow) instead of one (workflow) for your process. The delay can easily be removed, as explained above.

If you still want a calculation property, I would recommend you use the 'Time since' calculation property type for this:

Custom equations in calculation properties cannot reference the concept of today yet, so if you want to calculate the time since something happened, the above is the best practice and most reliable option.

Best regards

[franksteiner79 · Accepted solution]

Hi @TGibs02 

I feel your pain, I struggled with this until someone pointed me to the "add_time" function, see example below:

You do the following:

1. Property field type = calculation & custom equation
2. Output date
3. The use the "add_time" function (pick your "start" property, how much you add, and of what time format)

So in your case this should look something like: add_time('Client as of Date',30,"days").

Give that a try.

Frank