Post

16 followers Follow
1
Avatar

How to calculate the duration (number of days) between two date fields

In case this helps anyone else needing to calculate the number of days between two dates in an item, here’s the calculation field formula:

var d1 = moment(@StartDate);
var d2 = moment(@FinishDate);
var result = d2.diff(d1, 'days')+1;
result;

where @StartDate and @FinishDate are the date fields (in the formula, you need to replace the date variables text with an “@” and then select the date field from the Podio list of variables).

Also note that two separate date fields are required. This won’t work with a single date field that has both a start and end date (per someone else’s comments, Podio will only return the first date in this situation).

Roger Jesrani

Please sign in to leave a comment.

20 comments

0
Avatar

Do I copy and past that formula into the Calculation section and replace @Start Date with my titled Start Date etc?
Do I change anything else with the formula?
I tried to do what I listed above, but it says "Invalid Date"
Taylor@JenningsHP.com

Taylor Jennings 0 votes
Comment actions Permalink
0
Avatar

Hi Taylor, the above syntax works for me. Couple of suggestions:

  1. Check your syntax. (I know this is obvious, but you might find a typo.)
  2. Make sure you are using 2 date fields, not just one with a start and stop date.
  3. When you are looking at the formula in the Calculation field, the variables (each date field) should show up in a blue background with the "@" sign removed. This happens after you save the changes, etc.
  4. I can email you a screenshot of my exact formula if you like.

Thanks,

Roger

Roger Jesrani 0 votes
Comment actions Permalink
0
Avatar

Fantastic. I copied and pasted the entire thing into the calculation field, and changed the @ to my variables, and it worked! Perfect!

Ben Bakhshi 0 votes
Comment actions Permalink
0
Avatar

Glad to hear it Ben. I was happy when I got it to work the first time too! :-)

Roger Jesrani 0 votes
Comment actions Permalink
0
Avatar

Thank you very much, that was exactly what i was looking for!!

I have changed 'days' to 'weeks' and took out the +1 and it worked just fine, generating the result in Weeks instead.

Thank you again

Ibrahim S - Brazilian Experience 0 votes
Comment actions Permalink
1
Avatar

I tried copy and paste the formula and get: "The result is not a valid number".

I appreciate your help

Alí Hermes 1 vote
Comment actions Permalink
0
Avatar

Any progress on a method to get the number of weekdays between the Start and Finish date on the single field (rather than having to split into two separate fields?)

Oliver Ashford 0 votes
Comment actions Permalink
0
Avatar

so using this, i changed the @ to my variables and it consistantly kicks back 1. regardless of how many days between my start and finish date. I'm using 2 separate lines. This is driving me absolutely mad. Any help would be appreciated

Greg Vitale 0 votes
Comment actions Permalink
0
Avatar

Hi Greg,

can you please share a screenshot of your calculation. 

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi. This formula is exactly what I need so thank you, however I can't seem to get it working for me.  Do I paste all of this text in the calculation field:

var d1 = moment(@StartDate);
var d2 = moment(@FinishDate);
var result = d2.diff(d1, 'days')+1;
result;

... replacing the @ with the names of the fields in my form?  That is what I have tried but I can't get rid of an error.

Hope you can help :)

 

Roxy Gleeson 0 votes
Comment actions Permalink
0
Avatar

Hi Roxy,

can you please share a screenshot which shows the calculation field incl. the error notification?

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hey, would really appreciate some help here,  Me too am getting a kickback of 1 regardless of how many days between my start and finish date.

Also is there a work around if the start date and end date is the same therefore making the number of days 1

Maya 0 votes
Comment actions Permalink
0
Avatar

Hello Maya,

can you please share a screenshot of your calculation code and the code here?

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hey Rainer

This is what I used

dt1 = new Date(@Leave From Date);
dt2 = new Date(@Leave To Date);
(Math.floor((Date.UTC(dt2.getFullYear(), dt2.getMonth(), dt2.getDate()) - Date.UTC(dt1.getFullYear(), dt1.getMonth(), dt1.getDate()) ) /(1000 * 60 * 60 * 24))+1);

Leave From Date == StartDate

Leave To Date == FinishDate

I hope this helps

Thanks 

Maya

 

Maya 0 votes
Comment actions Permalink
0
Avatar

Sorry i should have shared this solution earlier. just got very caught up in work

Maya 0 votes
Comment actions Permalink
0
Avatar

Roger Jesrani your solution worked perfectly for me! However, I am trying to calculate a project completion period (how many days it took from the project start date to the project completion date. I have both fields in date format, and the math works great (I took off +1 so I can see "0" when a project was completed on the same day. This may be nit-picky, but I thought I'd ask: is there something I can add to this formula to make the field display "Same Day" if the calculation = 0? Thanks!

AJ Tavez 0 votes
Comment actions Permalink
0
Avatar

Hi AJ Tavez,

it depends on if you need that field for further math calculations. At the moment your calc field is of type "number", for showing "same day" you field must be of type "text" - means: A result like 6 would also be text (technically) and you have to parse it back to "number" if you want to use that field for math calcs in other calculation fields. 

To let the field show "same day" you must add a new calculation field cause once the type is saved you can't change it. 

var d1 = moment(@StartDate);
var d2 = moment(@FinishDate);
var result = d2.diff(d1, 'days');
result == 0 ? "Same day" : String(result)

You can also have to calc fields if you need the number for further calculations. Make the existing field Always hidden and use it and let the new field show the text. 

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink