Excel has lots of very cool functions for doing all sorts of calculations. One of these is the NETWORKDAYS function.
You can use it to calculate the number of days between two dates taking into account holidays.
Start by placing the dates for the holidays in a range of cells across a row or down a column. Select this range and name it holidays using Insert > Name > Define.
The function calculates the number of workdays between two dates so place one, for now, in cell A1 and the other in A2. This function will calculate the days between the dates in cells A1 and A2 taking into account the holidays listed in the range called Holidays:
=NETWORKDAYS(A1,A2,Holidays)
If the NETWORKDAYS function returns an error make sure that you have the Analysis Toolpak installed as this function is stored in this toolpak. To install it in Excel 2003 choose Tools > Add-ins and enable its checkbox. In Excel 2007, click the Microsoft Office Button > Excel Options > Add-Ins and from the Manage list choose Excel Add-ins and click Go. In the Add-Ins Available list enable the Analysis ToolPak checkbox and click OK.
Thanks very much this is very useful….