Counting days of the week excluding Sunday & Holidays
I am trying to count the number of days over a period of time that include Saturdays, but exclude Sundays and holidays. Is there a known formula for that?
This is a formula I found for excel but isn't working in SS:
=SUM(INT((WEEKDAY([LOAD IN START]@row - {2,3,4,5,6,7}) + [LOAD IN END]@row - [LOAD IN START]@row) / 7))
Best Answer
-
It automatically excludes your non-working days that are set on the sheets project settings. The default is mon-fri as working days but it can be changed in the project settings to mon-sat
Answers
-
Networkdays will give you the number of days between two dates based on your project settings excluding anything you have as a non-working day.
=NETWORKDAYS(Date@row, [Next Date]@row)
-
Thanks @Hollie Green - i only have dates set from start date and finish date, is there a way to create a formula to automatically identify Sunday and Holidays and remove that from day count?
-
If you go into your project settings the below article walks you through how to set it for your sheet. The Networkdays formula automatically excludes it from the number of days between the start date and the finish date.
It won't automatically figure out when the holidays are because different companies do different holidays so you will have to input your holiday dates.
-
@Hollie Green am i reading this wrong as it says it automatically excludes the weekend but I need it to include Saturday and only exclude Sunday. Sorry as usually i'm good with formulas but struggling with this one!
-
It automatically excludes your non-working days that are set on the sheets project settings. The default is mon-fri as working days but it can be changed in the project settings to mon-sat
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!