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:

    DECLARE(
    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:
    http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates


    ====== 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:
    http://partialclass.blogspot.co.uk/2011/07/calculating-working-days-between-two.html
  • 3 Comments
  •     steve August 24, 2012 5:27AM
    See also another method for counting the number of weekdays (Mon to Fri) between two arbitrary dates:
    http://forums.visokio.com/discussion/comment/6286/#Comment_6286
  •     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:

    SCRIPT(
    `
    start < end
    `,
    'start',TEXTTODATE('2012-08-01','yyyy-MM-dd'),
    'end',TEXTTODATE('2012-08-23','yyyy-MM-dd'))

    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.

Welcome!

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

Sign In Apply for Membership