Using NETWORKDAYS() to calculate the number of working days between two dates.

This is a common question particularly in Construction when you have a start date, and at some point in the distance an end date, and you need to know the number of working days between – so excluding all weekends and perhaps some holidays too.  This can be a time consuming task using a calendar if the dates are far apart, and is compounded if you have a long list of dates you need to compare.  Here’s where Excel or Google Sheets can really save you some time. NETWORKDAYS works exactly the same in Excel as it does in Google Sheets.  The arguments are NETWORKDAYS(Start Date, End Date, [Holidays]).  You will notice that the “Holidays” argument is in square brackets, this is indicating that this argument is optional. In our first example, let’s assume we’re ignoring the holidays but we want to exclude the weekends from our calculation: So we can see it is taking the start date from Cell A2, and the...
Read More

Sorting by 2 or more columns at the same time

In an earlier blog post we talked about how we can sort our data in Excel and Google Sheets by alphabetical or numerical order.  This was assuming you only wanted to sort by 1 column (e.g. sort a table of personnel records by Name in alphabetical order). But there are times when you will want to sort by 2 or more columns at once.  The technique is largely the same in Excel as it is in Google Sheets, although the menus are in different locations.  In this example we have a table I randomly downloaded from the Office for National Statistics, as we can see there are a number of ways we might want to sort this data: So let’s say for example we want this first sorted by “NUTS level” (whatever that means…) and then by Index from low to high.  First we select the table:  in Excel we click the “Sort & Filter” button in the Home tab and choose...
Read More

Compare two tables and find records without matches

So in this scenario, you have two lists, and you want to check one list against the other and look for missing values.  There could be many situations you could be faced with this when using Excel or Google Sheets; probably most frequently when error-checking. The way to do this is to use VLOOKUP, either alone or combined with IFERROR, or IF and ISERROR if you want the end result to look a bit more professional. So to begin with, let’s just start by using VLOOKUP (we have a blog post just about using VLOOKUP, so head on over to there to familiarise yourself [LINK]).  The technique will be exactly the same in Excel as it is in Google Sheets. So here we have List 1 and List 2 – we can clearly see that List 2 is shorter, and so therefore some items from List 1 are missing – but which ones? Using VLOOKUP alone: We can start by putting a checking column into...
Read More