Excel's Networkdays Alternative (Count days excluding Weekend) [version 2]

Version 4

    Several months ago, I wrote something relating to the Excel's Networkdays alternative.

     

    EXCEL's "networkdays" s alternative

     

    I recently realized that there are much simpler way to get same result.

    At the same time, I noticed this place 'Community Canvas Document" is better to post.

     

    The goals is to count "Weekdays" between two dates.

    In same meaning, count "Weekend days"  between two dates.

     

    Old approach was to exclude "Weekend" (Saturday and Sunday together ) from "Days Between".

    But actually it's much simpler to count Sunday / Saturday respectively.

    Any day of week can be counted actually.

     

    Take "Count Saturday" as example.

     

     

    [Saturday Count ]

    ceiling(max(

    0,

    datetrunc('week',[End Date],"Saturday")+1-datetrunc('week',[Start Date]+6,"Saturday")

    )/7)

     

    [Sunday Count]

    ceiling(max(

    0,

    datetrunc('week',[End Date],"Sunday")+1-datetrunc('week',[Start Date]+6,"Sunday")

    )/7)

     

     

    Now,

    [Weekend Days] = [End Date] - [Start Date] +1 - ( [Sunday Count] + [Saturday Count] )

    Thanks,

    Shin