Calculation between dates

Roland Abeling

January 09, 2015 17:22

I have a creation date of a pending task of a project.

Every day that passes, the pending was not treated in the field of days without treatment should add one day.

How is it possible to create as we do in MS-EXCEL, Start Date - End Date, and it returns the number in the Total days.

Can someone help me, or went through the same problem.

Thk.

Comments

8 comments

Rainer Grabowski (FVM)

January 09, 2015 18:11
var date1 = moment(@startDate_field); // e.g. 2015-01-09
var date2 = moment(@endDate_field); // e.g. 2015-01-31
date1.diff(date2, 'days') // result: 22 (as a number).

The result are calendar days, incl. weekends. If you want business days it's a bit more code.
0

Comment actions Permalink
Andy Baker

January 29, 2015 01:07
Rainer - How would this look it I wanted to reference another field to add up the sum of something between the date ranges?

For example: Sum of Distribution Amount between 1/1/5 - 12/31/15
0

Comment actions Permalink
Rainer Grabowski (FVM)

January 31, 2015 18:54
Hi Andy,

depends on the setup of your apps. You must have a field with a date in the App A from which you want to get the numbers. Then you can filter those entries between 1/1/15 and 12/31/15. Do you want to see the total amount for a fixed time span or should the time span be variable in the other App B where you want to sum the numbers?

VARIABLE TIME SPAN:
For a setup with a date field in App A and a variable time span, you have to create 2 date fields in App B ("span-start" and "span-end"). A code in the calculation field "Total Amount in time span .. " for this setup could be:
```
var date = @all of DateField; // date field in App A
var num = @all of NumberField; // number field in App A
var start = @Span-Start; //  // date field in App B
var end = @Span-End;  // date field in App B

total = 0;

for(var i = 0; i < num.length ; i++) {
if (date[i] >=  start && date[i] <= end) {
total +=num[i];
}
}
total;
```
The code above sums the numbers from those entries in App A which have a date that is greater (=after) than or equal to the "span-start"-date and less (= before) than or equal to the "span-end"-date. So you can change easily the time span by changing Span-Start or Span-end date for each item in app B individually.

FIXED TIME SPAN:
For a setup with a date field in App A and a fixed time span, the code in the calculation field "Total Amount in time span .. " in App B could be:
```
var date = @all of DateField; // date field in App A
var num = @all of NumberField; // number field in App A
var startFixed = moment("2015/01/01").toDate(); // start date of time span
var endFixed = moment("2015/12/31").toDate(); // end date of time span

totalFixed = 0;

for(var i = 0; i < num.length ; i++) {
if (date[i] >= startFixed && date[i] <= endFixed) {
totalFixed  +=num[i];
}
}
totalFixed ;
```
This second code above sums the numbers from those entries in App A which have a date that is greater (=after) than or equal to 2015/01/01 and less (= before) than or equal to 2015/12/31. The time span is fix to all items in App B (vs. individual in the first code).

You can also create solutions for e.g. fixed start date plus/minus x days/weeks/months or variable start date plus/minus (fixed) 6 days/weeks/months ...

Rainer
rg@delos-consulting.com
0

Comment actions Permalink
Andy Baker

February 02, 2015 16:03
Thank Rainer! This is good stuff ;)

At this point, if we have any further questions we may hit you up for some consulting. Is it safe to assume we can use the email below to connect with you directly?

rg@delos-consulting.com

Thanks again!
0

Comment actions Permalink
Rainer Grabowski (FVM)

February 02, 2015 17:32
Hi Andy,

yes, you can use that e-mail-address.

Rainer
0

Comment actions Permalink
John Trickett

March 12, 2015 13:02
How can this be done to calculate the number of weekdays between dates? I have the following at present:

var d1 = moment(@Start Date (calc));
var d2 = moment(@End Date (calc));
var result = d2.diff(d1, 'days')+1;
result;

But this only returns full days.
0

Comment actions Permalink
Rainer Grabowski (FVM)

March 12, 2015 13:13
Hi John,

I'm not sure what you mean by "weekdays" and "full days" .

If "weekdays" = "workdays" (Mon-Fri) than see my posting here
https://help.podio.com/hc/communities/public/questions/202828487-Calculating-workdays?locale=en-us

Rainer
0

Comment actions Permalink
Beth Barbaglia

October 23, 2020 19:57
I'm trying to get this calculation to work but I don't have an option for Start Date and End Date. I have one date field called "Dates Staying"...I don't have times enabled.

When I use the "@" symbol...it only lets me select the field Date Staying not the start date and end date separately. Any advice?
0

Comment actions Permalink

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?