It’s been a while since my last blog. So to get back into it, I thought I’d start a short series to answer some of the common, simple Excel questions that I am asked.
If you know me, you will know that I love a spreadsheet and can often be found using formulas and VBA to create awesome spreadsheets to automate and simplify everyday tasks.
In the office, it’s not uncommon to hear “Joe, can you help me with this formula?” or “Joe, how do I do this in Excel” or “Joe, Help! Please fix this spreadsheet for me!”
Of course, being the helpful geek that I am, I not only find a solution to the question in hand but usually offer fast improvements overall as well.
How do I work out how many work days are between two dates?
Calculating the days between two dates in Excel is very simple and I am sure that even an Excel Novice can come up with = Last_Date – First_Date to calculate the number of days between the two dates.
Now whiles this is a very simple and effective formula, often you want to know how many workdays (excluding weekends) there are between the two dates. This used to be a little trickier in older verions of Excel, but since Excel 2007 it became very simple indeed.
Introducing Excel’s =NETWORKDAYS : a very simple solution to your work day calculations
Let Excel calculate how many weekdays occur between two dates, excluding Saturdays and Sundays, easily with this simple formula:
=NETWORKDAYS( First_Date , Last_Date )
Wow, that was easy wasn’t it? But what about bank holidays I hear you say? No worry – Microsoft has thought of that too, with the addition of a further argument (holidays):
=NETWORKDAYS( First_Date , Last_Date , Holidays)
I find the easiest way to manage the holidays is within a list. Simply add a list of days to exclude.
You can tidy this up by turning the list range into a table so that it is easier to add new holiday dates as required.
To create your ‘holiday’ table, simple click any cell in your holiday list range and go to insert on the ribbon then click table (or CTRL + T as a shortcut). A window will then pop up for you to confirm the table area and whether you have a heading. Excel is usually very good at getting this correct, so ordinarily you can just click ok at this point.
You will now have a table looking something like this:
I’d recommend naming your table to something relevant – in this case “Holidays” would suit perfectly.
To do this, simply click a cell in your new table and go to ‘Design’ on the ribbon. And over on the left you can change the table name:
Your formula can now be amended to look like this:
=NETWORKDAYS( First_Date , Last_Date , Your_Table_Name )
Remember I said a table would make it easier to update? Try adding an additional date to the cell immediately beneath your table. You will see that the table automatically expands to include this extra date in your holiday list – Cool eh? Now go put it to work!
Sure a holiday list is brilliant… But don’t forget, those bank holiday dates do change every year. Do you want to manually change the list every year? Of course you don’t!
You can relatively easily add most bank holiday days (such as New Years Day, Bank Holiday Mondays, Christmas and Boxing Day) using a few formulas…but more on that very soon!
Easter is a littler trickier to add as it moves each year, but with some VBA it is completely possible.
Watch this space, for a new post coming soon!!