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

[Shuler]

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. 

[Shuler · Accepted solution]

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 🙂  

[Shuler · Accepted solution]

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 🙂  

[MiaSrebrnjak]

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

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

[GreyHamilton · Accepted solution]

@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: 

[ankitparmar09]

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 %}

[MiaSrebrnjak]

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