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

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