holiday - image credit: pixabay/Stephanie LaubscherMy students are great at finding me new tips to write. This week I had one who wanted to count the number of days between two dates but without the weekends. They had started to look at conditional formatting, filtering and other such tools. But these did not give them what they required. I thought that this would be something a lot of people might need so here one solution to the problem.

There are hundreds of functions held in Excel. One of them does just what we need here. It is the ‘Networkdays’ Function.

How to use the Network days Function.

Create a list of dates with the name of the day beside them so that it makes it very clear for you to see which dates are week-end ones. ListI have also highlighted the weekends to make it even easier to distinguish.

If you use the count function at the bottom of the second column you will have the result of all of the cells containing dates.

Here is the result after using the formula that reads. =COUNT(B1:B23) count daysUsing a different function such as NETWORKDAYS will result in only the week days being counted. networkdaysTyping a formula is quick and easy, but not always the best way. One reason is that you don’t get to see that there are other features that may well be helpful. Here is the dialog box that opens when you choose the NETWORKDAYS from the Insert Function Tool found either on the formula bar or in the Formula Tab. Insert Function Tool

  • Select this tool and this dialog box appears. Insert Function List
  • Locate the NETWORKDAYS function and select it.Network days functionThis dialog box appears. Function Dialog BoxIn this dialog box you have a start date, end date and even holidays that you can put into the formula. You can select cells in your spreadsheet if you have those dates or type in the dates you require. I have chosen a personal day to be 15th April, which is in the cell B8.
    Arguments filled
  • Press the OK button and the result will show in the spreadsheet.
    Resulting in this. Result 1

Tips – Count functions in Excel Continued

Tips – Using Conditional Formatting to colour a whole row.

LEAVE A REPLY

Please enter your comment!
Please enter your name here