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

Comments

21 comments

  • Taylor Jennings

    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

    0
    Comment actions Permalink
  • Roger Jesrani

    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

    0
    Comment actions Permalink
  • Ben Bakhshi

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

    0
    Comment actions Permalink
  • Roger Jesrani

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

    0
    Comment actions Permalink
  • James Goldbeck

    Worked great! Thank you very much

    0
    Comment actions Permalink
  • 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

    0
    Comment actions Permalink
  • Roger Jesrani

    Great - thanks for letting me know

    0
    Comment actions Permalink
  • Alí Hermes

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

    I appreciate your help

    1
    Comment actions Permalink
  • Oliver Ashford

    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?)

    0
    Comment actions Permalink
  • Greg Vitale

    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

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Greg,

    can you please share a screenshot of your calculation. 

    0
    Comment actions Permalink
  • Roxy Gleeson

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

     

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Roxy,

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

    Rainer

    0
    Comment actions Permalink
  • Kathrine N. Hansen

    Thanks a lot, The formula works perfect :-)

     

    0
    Comment actions Permalink
  • Maya

    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

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hello Maya,

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

    Rainer

    0
    Comment actions Permalink
  • Maya

    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

     

    0
    Comment actions Permalink
  • Maya

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

    0
    Comment actions Permalink
  • AJ Tavez

    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!

    0
    Comment actions Permalink
  • Rainer Grabowski

    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

    0
    Comment actions Permalink
  • Silvia Tejeda

    Hi Rainer could you help on how would I change this to count business days Rainer Grabowski

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk