I was recently reviewing some costings for a project which was quite heavily reliant on external staff paid at a range of day rates. The original creator of the spreadsheet had made what is a fairly common assumption that the project would take x months and that there are four weeks per month, so 20 days of each resource per month they were to be involved.
Now, at a high level, this would probably have been fine but having just been through the Christmas holiday period, which this year included a few extra Bank Holidays, due to standard holiday days falling over a weekend, I started thinking about how accurate the 20-day number was over an extended period.
Thankfully, Excel makes working this out easy with several functions designed for this purpose. However, being a bit of an Excel nerd, I decided to take things a bit further and create an ongoing calendar of working days into the future.
So let me start with some assumptions. This is based on a 5-day working week, Mon-Fri, with UK statutory bank holidays as the only other non-working time (although I’ll come back to that later).
As you can see from the end result, I’ve worked this based on the UK tax year, mainly because that is also the financial year for the organisation in question but that is an arbitrary choice, I could just have easily done Jan-Dec or indeed any other combination of start and end.
It also goes to March 2025 only because the UK government publish forward Bank Holidays only up to the end of 2025 at the moment although this can be updated once those are set in stone.
So each column of the table has four elements:
- Month
- Days in that Month
- End date of the month
- Working days for that month
It would be true to say that I didn’t need to have all of those elements shown in the table and could have simply used the formula all together, but this way seemed to be a bit clearer and allowed a bit of sense checking that the calculations were correct.
The month element is straightforward, I enter Jan-23 and then if I then select that and drag it automatically increments the month across. Similarly, for the full year lines I enter Apr-23 and then select drag that across 12 columns and it automatically populates the relevant month. One thing to watch here is that although it displays, for example, Apr-23, if you click the cell you see the value is 01/04/2023, however, if you enter that value directly and then drag across it increments the days of April rather than the month which isn’t what we want here.
From here on we are going to look at column J which is January 2023 but the same applies to the whole sheet.
The next line can just be entered manually if you can remember your “thirty days hath April” etc rhyme although that will not only test your memory but will also miss leap years and any other anomalies so instead, we use a function EOMONTH which as the name suggests returns the End Of MONTH for a given date.
=EOMONTH(start_date, months)
where
start_date is a date in a month, we are using the 1st, but it can be any date months is a number of months before or after the start date, but in our case, it will be 0
EOMONTH returns the date in the Excel serial date format so to make it useful we need to wrap it in the DAY function which converts a serial date into a number, in this case, it will be the number of days in that month.
=DAY(EOMONTH(J3,0))
For our example of 1st Jan it therefore returns 31. If you look ahead in the table above you will note that Feb-24 is a leap year, and so returns 29 days.
The next step is to generate the last day of the month. The only thing to remember here is that you have a start date of 1 and the number of days in the month so adding them will give you, for January, the 32nd! So, simply subtract one from the number of days and then add to the start date, in cell J5 we therefore have:
=J3+(J4-1)
We now have the first building blocks to make our actual calculation. To do this, we are going to make use of the function:
NETWORKDAYS(start_date, end_date, [holidays])
This will return the number of whole days between the start and end dates on the assumption that work days are Monday to Friday. So initially, J7 would look like this:
=NETWORKDAYS(J3, J5)
At this point, we haven’t yet looked at removing any holidays, so this will give us the maximum number of work days for the month.
Obviously to be useful for the intended purpose we need to now look at the holidays that need removing to get to the actual number of days available to us. For this, I created a separate tab and then listed all of the bank holidays for the period.
As I want this to be something that can be updated into the future, I created my list of holidays and then defined a named range (tblBankHoliday). The range is helpful because it allows for future updates without having to go back and edit any of the main page formulas; that is to say that updating the tblBankHoliday range will automatically update the existing formula.
So now we have our holidays laid out we can update our formula as follows:
=NETWORKDAYS(J3, J5, tblBankHoliday)
and hey presto we now remove any holidays to get to the actual number of working days available to us.
Of course, this is a fairly simple example as it uses only Bank Holidays but adding additional periods of non-activity are as easy as entering them into the named range.
It is worth noting that the dates don’t have to be sequential so if you want to add in other dates having created your initial list they can simply be tacked onto the end and they will be taken off the working days available. This could be useful if for example, there is a pause to a project or the site will be closed for some reason.
I noted earlier that the assumption here was for a 5-day working week, Monday to Friday. This won’t always be the case of course, sometimes there may be alternative days or it may be a longer or shorter working week. To deal with those there is an alternative function, NETWORKDAYS.INTL that works in broadly the same way but with one additional element:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
The three familiar fields work in exactly the same way, but the new weekend field allows the selection of any combination of days to be “weekend”. The full list of possibilities is beyond the scope of this article but suffice to say it can create any working pattern that is based on whole days.
Hopefully, that will be useful for some future project.
Epilogue – Was it worth it?
The inevitable question from something like this is; was it worth the time?
The longest part of setting up the spreadsheet was adding the Bank Holidays into the holiday tab. There is probably a web-based list of these that could be imported automatically but the time to find out would probably have exceeded the time to input manually. So in that sense, it was a low time cost solution.
But is 20 days as an estimate that far off? Not really, over the period included so far the average days per month are 21.1 (the leap year does slightly skew this, it would be closer to 21.2 without it), so did it make a difference?
The answer is yes. For the project in question, there were a range of day-rate resources ranging from £300 to £1200 per day. So over the course of 6 months or so even the extra 1 or 2 days per month added several thousand pounds to the project cost which would otherwise not have been budgeted.
Also, now that it exists, maintaining it is as easy as entering holidays as they get released and copy and pasting the next financial year into the main sheet.
