How to calculate networkdays for each month between a Start Date and End Date?

sweta22

I have columns for Start Date and End Date. I would like to calculate the number of working days for each month within the date range specified by these columns and display the results in a different sheet where the months are listed as columns. Could someone provide a formula or suggest an alternative approach to achieve this? Any help would be greatly appreciated.

mcarlson

To calculate the number of working days (network days) for each month between a start date and an end date in Smartsheet, you’ll need to approach this by calculating the network days within each month separately. Smartsheet doesn’t directly support monthly network day calculations, so here’s a workaround using helper columns and conditional logic.

Steps to Calculate Network Days for Each Month

1. Set Up Your Date Range:
   • Let’s assume you have Start Date and End Date columns in your main sheet.
2. Create Helper Columns for Each Month:
   • In the target sheet where each month is listed as a separate column (e.g., January, February, March, etc.), create helper columns to calculate the first and last days of each month.
   • For example, you could name them Jan First, Jan Last, Feb First, Feb Last, etc.
3. Use NETWORKDAYS Formula for Each Month:
   • For each month column, use the NETWORKDAYS function, and determine the relevant date range within the start and end dates.
   • excelCopy code=IF(AND([Start Date]@row <= DATE(YEAR([Start Date]@row), 1, 31), [End Date]@row >= DATE(YEAR([Start Date]@row), 1, 1)), NETWORKDAYS(MAX([Start Date]@row, DATE(YEAR([Start Date]@row), 1, 1)), MIN([End Date]@row, DATE(YEAR([Start Date]@row), 1, 31))), 0)
   • Here’s a breakdown:
     • DATE(YEAR([Start Date]@row), 1, 1) represents January 1 of the start year.
     • DATE(YEAR([Start Date]@row), 1, 31) represents January 31 of the start year.
     • MAX([Start Date]@row, DATE(YEAR([Start Date]@row), 1, 1)) calculates the later date between the Start Date and January 1, ensuring you only count days within the start of the date range.
     • MIN([End Date]@row, DATE(YEAR([Start Date]@row), 1, 31)) calculates the earlier date between the End Date and January 31, ensuring you only count days within the end of the date range.
4. Repeat for Each Month:
   • Duplicate this formula for each month, updating the month number in the DATE() function accordingly.

Final Notes

• This approach requires individual formulas for each month, which makes it somewhat manual, but it ensures accurate network days for each month.
• Alternative Option: If you have access to Excel or Google Sheets, this calculation can be done more efficiently with scripting or Power Query, then you could import the results back to Smartsheet for tracking.

Using this method will give you a breakdown of network days for each month, displayed in separate columns as you requested. Let me know if you'd like further help setting up these calculations!