dategoogle-sheetsgoogle-sheets-formulaweekend

How to calculate the weekend when counting a date range in Google Sheets?

I have the below columns

StartDate   EndDate    CountDay
01 May 20   05 May 20     ?

As you see, 01 May is Friday, so from 01-05 May if we count all days including weekend it will be 4 days.

What I want is on column "CountDay" it only counts the Workdays, not the weekend.
SO the expected result would be 2.

Anyone know how to do it using a formula in Google Sheets?

Solution

  • Do you consider Fridays as part of the weekend?

    If yes, then you could also try the following formula: 

    =NETWORKDAYS.INTL(A10, B10,"0000111")

    weekend including Fridays as weekend

    If not, please use this formula: 

    =NETWORKDAYS.INTL(A10, B10)

    How the formulas work.

    By using the function NETWORKDAYS.INTL we can "adjust" the weekend (non-working weekdays) to our liking.
    In this case we account Fridays as our non-working weekdays by using as the 3rd parameter 0000111 instead of the default 0000011 where every 0 represents a working weekday and every 1 a non-working weekday.

    (Very useful for people working part-time)

    Someone who has part-time work on only Mondays, Wednesdays and Fridays and wants to calculate the working days Friday, 1 May 2020 - Tuesday, 30 June 2020 could adjust the formula to: 

    =NETWORKDAYS.INTL(A10, B10,"0101011")

    Calculating part-time workdays for only Mndays, Wednesdays, Fridays

    As explained on the official Google help page for NETWORKDAYS.INTL

    weekend – [ OPTIONAL – 1 by default ] – A number or string representing which days of the week are considered weekends.

    • String method: Weekends can be specified using seven 0s and 1s, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends.
    • Number method: Instead of using the string method above, a single number can be used. 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday and this pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend day, 12 = Monday is the only weekend day and this pattern repeats until 17 = Saturday is the only weekend day.