Does anyone know a way to calculate the business days between 2 dates in knack?
1 Like
I found this javascript code that might help. I know we can utilize javascript in our apps, but I am not sure how to make it work. In my scenario, I am using this to calculate a metric for how many business days has gone by from one activity to the next.
function calcBusinessDays(dDate1, dDate2) { // input given as Date objects
var iWeeks, iDateDiff, iAdjust = 0;
if (dDate2 < dDate1) return -1; // error code if dates transposed
var iWeekday1 = dDate1.getDay(); // day of week
var iWeekday2 = dDate2.getDay();
iWeekday1 = (iWeekday1 == 0) ? 7 : iWeekday1; // change Sunday from 0 to 7
iWeekday2 = (iWeekday2 == 0) ? 7 : iWeekday2;
if ((iWeekday1 > 5) && (iWeekday2 > 5)) iAdjust = 1; // adjustment if both days on weekend
iWeekday1 = (iWeekday1 > 5) ? 5 : iWeekday1; // only count weekdays
iWeekday2 = (iWeekday2 > 5) ? 5 : iWeekday2;
// calculate differnece in weeks (1000mS * 60sec * 60min * 24hrs * 7 days = 604800000)
iWeeks = Math.floor((dDate2.getTime() - dDate1.getTime()) / 604800000)
if (iWeekday1 <= iWeekday2) {
iDateDiff = (iWeeks * 5) + (iWeekday2 - iWeekday1)
} else {
iDateDiff = ((iWeeks + 1) * 5) - (iWeekday1 - iWeekday2)
}
iDateDiff -= iAdjust // take into account both days on weekend
return (iDateDiff + 1); // add 1 because dates are inclusive
}
Hi Jeff,
I actually messed with this problem for a little while this weekend It's more complex then I made it out to be. There are multiple scenarios that each has a solution. Then the correct solution would be chosen conditionally. I got the main one working (more than 1 week) and then realized I had to account for at least 3 possibilities. Here are the scenarios. The ultimate solution using Knack's tools would be 20-30 fields long ( I haven't finished it yet) I like solving problems like this but haven't had the time.
A - More than 1 week
B - Less than 1 week - Not crossing weeks
C- Less than 1 week - Crossing Weeks
Seems cumbersome to do with fields and better handled with an API call to outside service or a script. Here is a head start on the javascript solution:
https://stackoverflow.com/questions/4600034/calculate-number-of-weekdays-between-two-dates-in-java
or you could do it via an API call here; https://www.timeanddate.com/services/api/
Good luck!
Thanks Justin! I will give it a try this week. ~ Jeff
I think it would look something like this. I haven't tried it so you might need to mess around with the 'complete weeks' formula to get it working. Someone may have an easier solution but this approach should work with some trial and error. I'm essentially trying to find the # of complete weeks and assign them each a value of 5 and then add the incomplete week ( # biz days) to it. Good luck :)
Start Date =Date Field
End Date = Date Field
Difference [equation field) = ({End Date} - {Start Date}) result type =Number measured in Days
Day of Week (1) = text formula ({getdayofweek(StartDate)})
Day of Week (2) = text formula ({getdayofweek(EndDate)})
Business Left in start Week = # field (conditional) [ if day if week is Wed then =2, If is Friday =0 etc]
Business Left in End Week = # field (conditional) [ if day if week is Wed then =2, If is Friday =0 etc]
Business past in start Week = [# field] (conditional) [ if day if week(1) is Wed then =3, If is Friday =5 etc]
Business past in End Week = [# field ] (conditional) [ if day if week(2) is Wed then =3, If is Friday =5 etc]
Total past biz days [formula ]= ({Business past in start Week} + { Business past in End Week})
Complete Weeks [formula ] =( ({Difference} - {Total past biz days}) /7)
Result = ({complete weeks} *5}) + (Business Left in start Week + Business past in End Week)