topic Re: Calculate difference between start date and end date in a custom object in Data Hub

Calculate difference between start date and end date in a custom object

Shuler — Fri, 31 Mar 2023 21:09:22 GMT

I have created a custom object in HubSpot, which has a start and end date - the calculated field can check for the time between however we run into an issue where if it's the same day, we get a 0 value when we would want to see a 1 value.

I've tried to do some custom code via a workflow to calculate this, but I am not a programmer and have no idea how to get something like this to work. What I am trying to do is if the start date and end day is the same, make the field say 1, if they are not the same dates, then calculate the difference.

Any help into this would be greatly appreciated.

Betreff: Calculate difference between start date and end date in a custom object

MiaSrebrnjak — Mon, 03 Apr 2023 12:28:23 GMT

Hi @Shuler,

Thank you for reaching out to the Community!

I wanted to tag in a couple of subject matter experts to see if they have any input on this matter:

Hi @deepikaverma, @GreyHamilton, @GCasali, do you have any advice for @Shuler? Thank you!

Cheers
Mia, Community Team

Re: Calculate difference between start date and end date in a custom object

ankitparmar09 — Mon, 03 Apr 2023 14:18:53 GMT

Hello @Shuler

Thanks for reaching out. You can use the code, it can help you.

{# Date Format Code #}
{% set date = event.event_end_date %}
{% set dateArr = date|split("/", 1000) %}
{% set useDate = [
(dateArr[2]|int + 2000),
(dateArr[0]|length == 1 ? '0' + dateArr[0] : dateArr[0]),
(dateArr[1]|length == 1 ? '0' + dateArr[1] : dateArr[1])
]
%}

{% set enddate = (useDate[0] + "-" + useDate[1] + "-" + useDate[2] + "T14:31:30+0530")|strtotime("yyyy-MM-dd'T'HH:mm:ssZ")|unixtimestamp %}

{% set today = (local_dt|unixtimestamp) %}

{% if (enddate <= today ) %}

// Code here


{% endif %}

Re: Calculate difference between start date and end date in a custom object

Shuler — Mon, 03 Apr 2023 15:19:44 GMT

Thanks @MiaSrebrnjak and @ankitparmar09 - I wanted to share that I came up with a solution and it works quite well to what I need.

Via the API, I created a calculation field with an if then condition. It calculates the difference between the times and if they are 0, it will output 1, otherwise I do the calculation, divide it by the miliseconds and get days. It just outputs a number, but this is exaclty what we need and want. Code below:

{ "name": "duration_days", "label": "Duration Days", "type": "number", "fieldType": "calculation_equation", "groupName": "pipeline_milestones", "calculationFormula": "if end_date-start_date=0 then 1 else (end_date-start_date)/86400000" }

This field then shows up just calculating the difference in days, and we're all set to report on it 🙂

Re: Calculate difference between start date and end date in a custom object

GreyHamilton — Mon, 03 Apr 2023 20:48:58 GMT

@Shuler Love this solution. I believe yours is the ideal solution, but for anyone who isn't comfortable with APIs or custom code I figured out a way to do it with a 'Days Between' calculated field and the Format feature in a workflow.

Here is the workflow utilizing the Format function. I tagged this as "Original Duration" as you could have it not re-enroll so it's only set once. Depending on if your dates change, you could potentially benefit from a "snapshot" of the original duration. The round was added because it was adding a 0 decimal point even though the property limited them.

Results:

Re: Calculate difference between start date and end date in a custom object

MiaSrebrnjak — Tue, 04 Apr 2023 09:20:51 GMT

That's great, thank you for sharing @Shuler!

And thank you for your help @GreyHamilton and @ankitparmar09, we really appreciate it! 💛