Excel - calculating workdays with Networkdays

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.

Random numbers in Excel

When you’re testing a workbook it is handy to be able to fill a set of cells with a sample value. The Randbetween function included in the Analysis Tookpak does this for you.

To enable this, choose Tools, Add-Ins and enable the checkbox for the Analysis Toolpak. Now write the function, for example, this provides a random number between 1 and 100 (including both numbers):

=randbetween(1,100)

Copy it to all the cells to fill. To fix the values so they don’t change each time something in the worksheet changes, select the cells with the formula, choose Edit, Copy then Edit, Paste Special, Values.