Formula for calculating differences between dates on coach

Jacob Edelman

September 23, 2016 at 9:13 am #2752

I am trying to write a formula in a Data CV that would calculate the difference between two dates selected in Date Selector CV’s on a coach.

Currently I have just @[email protected] but that does not seem to be working.

Any suggestions?

SPARK Support

September 23, 2016 at 10:09 am #2754

Hi Jacob,

The Date values return as a string. Therefore you are unable to take the difference as in your example above.

Depending on the format that you have your Date set to, I would create a function that concatenates the date into the month, day and year. Then use a switch statement and some logic to evaluate the difference between years, months, and days.

Regards,

Stephen P.

Jacob Edelman

September 23, 2016 at 11:44 am #2758

OK, so I created a function createDateFromDateString():

<script>
function createDateFromDateString(dateString){
var date = new Date(dateString);
return date;
}
</script>

And I am using this in the data CV formula to track the difference in the dates:

createDateFromDateString(@{StartDate}).getTime()-createDateFromDateString(@{EndDate}).getTime()

But I am getting no value. This notice (not error) is shown in the console:

Error executing formula expression for view /Data1 (UID=8): Default value get function mapping cannot be found on view: /StartDate – call setupFormulaTriggerUpdates and pass the default ‘value get’ function as the 3rd argument

SPARK Support

September 26, 2016 at 2:33 pm #2775

Hey Jacob,

If you wanted to get the difference in the Value Formula configuration option of a Data control, you could use this:

Math.floor((${Start}.getDate() – ${End}.getDate()) / (1000*60*60*24))

This will round down to the nearest day. Using Math.round will round up.

If you’re using a function, then you can use the getTime method, but that will return milliseconds. You can refer to this w3schools article on how to use the getTime method to return the difference in days, months, or years from two dates:

Javascript getTime Method

-Erick Q