You can definitely do it through workflows custom coded actions but as far as I’m aware, you there’s really not much mathing that you can do without custom coded actions (just increment, I believe).
You can do it via calcuation properties IF you really wanted to but it can get complicated.
Presuming:
1. Join Date property (date field)
2. Leave Date property (date field)
3. Salary field (number)
Calcuation Property Method:
You can do it using a calcuation - custom equation property (gratuity).
We start the formula using time_between the first and second fields. Date fields are unix timestamps in milliseconds so we divide by 86400000 to get days and then +1 to add current day.
time_between([properties.date_start], [properties.date_end]) / 86400000 + 1
Now that you have the days between two points, you can do the rest of the formula which should just be mathing around these two fields and maybe using IF statements if you wanted to be more concise.
Eg,
if (
(
(time_between([properties.date_start], [properties.date_end]) / 86400000) + 1 >= 365 AND
(time_between([properties.date_start], [properties.date_end]) / 86400000) + 1 <= 1825
),
[properties.salary]/365 * 21,
if (
time_between([properties.date_start], [properties.date_end]) / 86400000 + 1 > 1825,
[properties.salary]/365 * 30,
0
))
I don’t know what formula you’re using or what other “special” secenarios you want to consider (eg, this counts all days between two dates so it includes weekends and holidays not just “working days”).
If a long complicatedm multi-if statement is too unweidly, you could perhaps create multi grauitity fields that sum up into one field.
Workflow Method using Custom Coded Actions:
1. Create a new workflow
2. Create a Custom Coded Action Node
2a. Pass in the fields you need
2b. Do the code for the formulas (you can probably chatgpt it). Just sample code here:
exports.main = async (event, callback) => {
/*****
Use inputs to get data from any action in your workflow and use it in your code instead of having to use the HubSpot API.
*****/
const millisecondsInADay = 1000 * 60 * 60 * 24;
const dateDiffInDays = event.inputFields['date_end'] - event.inputFields['date_start'] * millisecondsInADay + 1 //Date fields are in unix_timestamp milliseconds format so we convert to days to be useful
var gratuity = 0 //default to 0
if (dateDiffInDays >= 365 && dateDiffInDays < 1825) { //If greater than 1 year but less than 5 years
gratuity = event.inputFields['salary'] / 365 * 21
} else if (dateDiffInDays >= 1825) { // Greater than 5 years
gratuity = event.inputFields['salary'] / 365 * 30
}
/*****
Use the callback function to output data that can be used in later actions in your workflow.
*****/
callback({
outputFields: {
gratuity: gratuity
}
});
}
Which would output a gratuity value.
3. Add a new action to update a property and push the gratuity value into that property.