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).
-
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 -
Hi Taylor, the above syntax works for me. Couple of suggestions:
- Check your syntax. (I know this is obvious, but you might find a typo.)
- Make sure you are using 2 date fields, not just one with a start and stop date.
- 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.
- I can email you a screenshot of my exact formula if you like.
Thanks,
Roger
-
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 :)
-
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
-
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!
-
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
-
Hi Rainer could you help on how would I change this to count business days Rainer Grabowski
Please sign in to leave a comment.
Comments
21 comments