How to Use the Smartsheet NETWORKDAYS Project Management Formula
In Smartsheet project management, accurately calculating the number of working days between two dates is crucial for planning and scheduling tasks effectively. To assist with this detail, Smartsheet offers the NETWORKDAYS and NETWORKDAY functions, which make it easy to determine the number of working days and adjust for non-working days and holidays. In this blog post, we will explore how to use the NETWORKDAYS formula by itself and as part of an IF function, as well as how to customize working days at both the organization and sheet levels.
Understanding the NETWORKDAYS Formula
The NETWORKDAYS formula in Smartsheet allows users to calculate the number of working days between two dates. It excludes weekends (Saturday and Sunday) and, optionally, any specified holidays or non-working days. The result returned by the NETWORKDAYS formula represents the total working days, making it an invaluable tool for determining task durations, project timelines, and resource allocation.
Using the NETWORKDAYS Formula
The syntax for the NETWORKDAYS formula is as follows:
NETWORKDAYS(start_date, end_date, [holidays])
Here's what each parameter means:
- `start_date`: The starting date of the date range.
- `end_date`: The ending date of the date range.
- `[holidays]`: (Optional) A list of holidays or non-working days to be excluded from the count.
For example, if you want to calculate the number of working days between two dates, say from August 1, 2023, to August 15, 2023, and you want to exclude August 7, 2023, which is a public holiday, you can use the formula:
`=NETWORKDAYS(DATE(2023, 8, 1), DATE(2023, 8, 15), DATE(2023, 8, 7))`
This formula will return the total working days between the given dates, excluding August 7, 2023, from the count.
Using the NETWORKDAY Formula
Similar to NETWORKDAYS, Smartsheet also offers the NETWORKDAY formula, which returns the number of working days between two dates but adds 1 day to the result if the start date falls on a non-working day (e.g., a weekend or specified holiday). This is particularly useful when you want to count the start date itself as a working day.
The syntax for the NETWORKDAY formula is the same as NETWORKDAYS:
NETWORKDAY(start_date, end_date, [holidays])
Customizing Working Days at the Organization Level
Smartsheet allows you to customize working days at both the organization level and the sheet level. Customizing working days at the organization level will impact all sheets that use project settings.
Here's how to do it:
- Go to the Admin Center in Smartsheet.
- Under Working Days & Resources, select Working Days.
- Check or uncheck the working days based on your organization's schedule. By default, Monday through Friday should be checked for a standard 5-day work week. However, you can edit this to accommodate a 4-day or 6-day work week, as needed.
- Optionally, you can list other non-working days, such as holidays, by entering the dates manually or using the date picker for that section. Remember to update these dates each year.
Customizing Working Days at the Sheet Level
You also have the option to customize working days for individual sheets. This customization will apply only to the specific sheet.
Here's how to do it:
- Open the sheet in Smartsheet and make sure you are viewing it in Gantt View.
- Use the settings icon in the top right corner to access the project settings.
- Under Working Days, select edit.
- Customize the working days and non-working days as needed.
Using NETWORKDAYS in an IF Function
The NETWORKDAYS formula can be combined with other functions, such as the IF function, to create more sophisticated calculations. For instance, you might want to determine whether a task is critical based on the number of working days required for its completion.
Consider the following example:
`=IF(NETWORKDAYS([Start Date]@row, [End Date]@row) > 5, "Critical", "Not Critical")`
In this formula, if the number of working days between the start date and end date of a task is greater than 5, the task will be labeled as "Critical"; otherwise, it will be labeled as "Not Critical."
Practical Examples with Project Plan Sheet
To gain a better understanding of how to use the NETWORKDAYS and NETWORKDAY functions by themselves, as well as within an IF function, you can refer to a tutorial video. The video showcases specific examples using a project plan sheet, highlighting the step-by-step process of calculating working days and adjusting for holidays and non-working days.
Improving Clarity with the Smartsheet NETWORKDAYS Formula
The Smartsheet NETWORKDAYS and NETWORKDAY functions are valuable tools for project managers to accurately calculate working days and determine task durations. By utilizing these formulas in combination with other functions, such as IF, project managers can create more customized and sophisticated calculations to streamline their project planning and scheduling processes. Additionally, customizing working days at both the organization and sheet levels allows for more accurate and flexible project management.
Smartsheet Project Management Solutions from CloudWave
If you’re considering Smartsheet for project management and data analysis, look for a Smartsheet Platinum Partner like CloudWave who understands the technology best practices and prioritizes your unique business needs.
Through Smartsheet integration, we deliver cross-platform functionality that helps you bring your system together for an end-to-end solution within Smartsheet. Using automation, we reduce tedious manual processes to save time and improve productivity.
CloudWave also provides Smartsheet support throughout the entire project lifecycle - defining scope, architecture, design, development and deployment. As your data environment constantly changes, we offer continued support beyond the project in an ever-shifting digital landscape.