File to download: Holiday.xlsm
Both the WORKDAY and NETWORKDAYS functions are essential for project planning. The WORKDAY function allows you to calculate the completion date provided that you know the number of days that is required to complete the project. The NETWORKDAYS function does the opposite. You have a desired completion date and you want Excel to calculate how many working days from the starting date to the desired completion date. By using either function, you can have better ideas how to manage the available resource or if you need to ask for more resources to commit to such project.
Syntax: WORKDAY(start_date, days, [holidays])
You can omit the last parameter (holidays) if you only want to use Excel to exclude the weekends from calculations. However, it is definitely more practical if you can include the holidays. The holiday parameter can be only one cell if you expect there is only one holiday within the project span, or you can put in a range of holidays like from A1:A10. The range of holidays can be beyond the project life span. For example, the project may last for 3 months but you can include a whole year of holidays. Excel will pick up the relevant holidays and exclude the ones that are beyond the project life span.
Below illustration shows the calculation of WORKDAY function. The completion date will be quite different if you exclude the holidays from the calculation, especially if the project proceeds during the holiday season. The last parameter (‘Holiday List’!F2:F51) is calculated separately using a VB module that I created. Please refer to this article (US Holidays Calculation) for more information.
Syntax: NETWORKDAYS(start_date, end_date, [holidays])
Below illustration shows the use of NETWORKDAYS. It is used to calculating the number of working days if you put in a desired Project End Date. It shares the same logic as WORKDAY but just works the opposite way.