Visokio website     Downloads     Video tutorials     KnowledgeBase  
Demo: Business Days Between Two Dates - Visokio Forums
Demo: Business Days Between Two Dates
  •        CRead August 23, 2012 7:13AM
    **Edited to post an improved method, as I found in some cases the JavaScript gave odd results.**

    This formula gives the same result as the NETWORKDAYS() function in Excel for all the dates I tested:

    ve,DECLARE(de,DATETOTEXT([End Date],'E'),IF(de='Mon',0,de='Tue',1,de='Wed',2,de='Thu',3,de='Fri',4,de='Sat',5,6)),
    vs,DECLARE(ds,DATETOTEXT([Start Date],'E'),IF(ds='Mon',0,ds='Tue',1,ds='Wed',2,ds='Thu',3,ds='Fri',4,ds='Sat',5,6)),
    5*INTFLOOR(DATEDIFF([Start Date],[End Date])/7) + VALUE(MID('1234555512344445123333451222234511112345001234550', 7*vs + ve + 1, 1))

    Source of inspiration:

    ====== Old version, recommended against! ========
    Passing dates as dates into a script can cause odd behaviour in JavaScript. This demonstrates passing them as text, then converting in JavaScript to circumvent those issues.

    The result is a calculation of the number of business days between two dates.
    The script itself is a copy of:
  •     steve August 24, 2012 5:27AM
    See also another method for counting the number of weekdays (Mon to Fri) between two arbitrary dates:
  •     steve August 24, 2012 5:31AM
    (Colin, what odd behaviour did you have with dates in JS?)
  •        CRead August 24, 2012 6:03AM
    This returns false:

    start < end

    The example you posted before suffers from issues that result from this. My solution is to convert the date to a string, and convert the string to a date inside the javascript. This fixes the issue.


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership