Being able to count networkdays by month in a range that covers two months!
Hi there
I'm trying to create a calculation of a date range... for example
So I have a networkdays formula counting the working days between the start date and end date. However, what I'm looking for is a formula that will count the network days for each month, for example above, June in a column, and the July dates in another column.
Any help would be greatly appreciated!
Many thanks
Jo
Answers
-
Would it ever need to span more than two months?
-
Hi Paul
No it wouldn't.
Thank you
-
Ok. Give this a try...
First Month:
=NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1)
Second Month:
=NETWORKDAYS(DATE(YEAR([End Date]@row), MONTH([End Date]@row), 1), [End Date]@row)
-
Hi
Thank you so much but unfortunately it didn't work.
It is counting the network days left in the rest of the month, not the network days between the date range. What I'm trying to achieve is a total count of all employees leave separated by month.
Thank you so much for trying though.
Best wishes
Jo
-
Right. You need two separate columns. One for the number of days in the first month and another for the number of days in the second month.
The number of days in the first month would be the remaining number of days in the month.
-
Hi @Paul Newcome
Nice solution!, I need something similar but separating the qty of NETWORKDAYS into the calendar year across multiple years. How do i need to modify this to make it work?
Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!