CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formulaweekend
noobsee
noobsee

Reputation: 962

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?

Upvotes: 1

Views: 3691

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

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.

Upvotes: 2

noobsee
noobsee

Reputation: 962

I just found how to do it:

=if(weeknum(A10)<weeknum(B10),B10-A10-2*(weeknum(B10)-weeknum(A10)),B10-A10)

something like that

Upvotes: 0

Related Questions