NETWORKDAYS definable at row level??
I'm using the useful template located at: https://www.smartsheet.com/marketplace/us/templates/team-vacation-planner
The only issue I have is that NETWORKDAYS are defined at a global level it seems in Smartsheet settings, which by default are excluding Saturdays and Sundays, which is perfect for my full time staff.
However, how can I adjust NETWORKDAYS as a formula at row level so that part time staff working different days can still get their NETWORKDAYS recorded accurrately between two date entries. For example, we have a staff member who works Tuesdays, Wednesdays and Thursdays, so i'd like to set the formula at her row level for NETWORKDAYS to add Mondays and Fridays to the already excluded Saturdays and Sundays.
The formula I have for NETWORKDAYS is basic:
=NETWORKDAYS([Start Date]18, [End Date]18)
for calculate networkdays between two dates on row 18
How could I amend this cell formula to count networkdays that fall outside of Tuesdays, Wednesdays, Thursdays??
It could either work directly as adjusted cell formula or reference separate cells that highlight the work days of an individual and apply the cell reference to the formula.
Any help would be much appreciated.
Many Thanks
Paul
Best Answer
-
Hi @prjessy
NETWORKDAYS is configured to a 5 day work week with Saturday and Sunday excluded, as you've found. The only way to exclude other days/dates from the count is by adding these as holiday dates at the end of the formula:
NETWORKDAYS( start_date, end_date, holidays)
If you have Project Settings enabled on this sheet with Dependencies, the function will also take into consideration the Holidays you've set for the sheet as a whole.
See NETWORKDAYS Function for more detail.
In this instance, what I would do is have a column set up either on a reference sheet or in this current sheet that lists out every Monday and Friday for the rest of the year. Then you can use an IF statement to check who the Staff is listed as, and if it's a T/W/Th staff member, use the NETWORKDAYS that excludes M/Fr, like so:
=IF([Staff Name]@row = "Genevieve P.", NETWORKDAYS([Start Date]@row, [End Date]@row, {Genevieve Holidays}), NETWORKDAYS([Start Date]@row, [End Date]@row))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @prjessy
NETWORKDAYS is configured to a 5 day work week with Saturday and Sunday excluded, as you've found. The only way to exclude other days/dates from the count is by adding these as holiday dates at the end of the formula:
NETWORKDAYS( start_date, end_date, holidays)
If you have Project Settings enabled on this sheet with Dependencies, the function will also take into consideration the Holidays you've set for the sheet as a whole.
See NETWORKDAYS Function for more detail.
In this instance, what I would do is have a column set up either on a reference sheet or in this current sheet that lists out every Monday and Friday for the rest of the year. Then you can use an IF statement to check who the Staff is listed as, and if it's a T/W/Th staff member, use the NETWORKDAYS that excludes M/Fr, like so:
=IF([Staff Name]@row = "Genevieve P.", NETWORKDAYS([Start Date]@row, [End Date]@row, {Genevieve Holidays}), NETWORKDAYS([Start Date]@row, [End Date]@row))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!