One of the tasks I often have to do in my working life is to create project plans. For simple plans I often find myself building them in Excel. Now I know before you start that there are more appropriate tools to build project plans – I know that applications like MS Project offer far more features and functions for creating project plans properly – however, for crude, simple plans I often resort to trusty excel. This is especially true when I’m sharing plans with colleagues that are not proficient in MS Project.
When using Excel, one of the problems I often hear mentioned is the fact that Excel doesn’t account for working patterns i.e. weekends or other days when adding values to dates. This can be a problem when you’re adding task duration to a start date.
Well, you might be surprised to find out that it does! Excel does come with some funky functionality allowing you to offset days when adding a value to a date.
It’s the WORKDAY.INTL Function
Let’s take a look at how it works.
The WORKDAY.INTL formula supports adding days to a date in excel but uses offset values that you provide ignoring them when adding the value. The offset can be standard working days or holidays.
Why use the WORKDAY.INTL Function formula?
In standard Excel formula by adding a value to a date Excel ignores weekends and holidays and merely adds the value to the date provided.
For example, the standard calculation would be if you start with 1/7/2018 and add 21 days you’ll get 22/7/2018. The fact that there are weekends in between is irrelevant to excel using the standard calculation. Now this is perfectly fine, but for simple project plans you’ll probably want to exclude certain days (often weekends) so using the standard formula won’t necessarily give you the answer you’re looking for.
So, by using the WORKDAY.INTL this allows you to set a working pattern in days worked and not worked. It also offers the opportunity to exclude certain dates from the calculation, great if you want to ignore holiday dates.
The syntax for the formula is as follows:
=WORKDAY.INTL (start_date, days, [weekend], [holidays])
Let’s take a look at an example.
In the screenshot above we’ve used two different methods of adding 21 days to our start date (1/7/2018).
The first example shown in F6 shows 1/7/2018 + 21 – this results in 22/7/18
The second example shown in F8 uses the formula =WORKDAY.INTL(D8,E8,”0101011″,A6:A8)
D8 is the start date for the formula (1/7/2018) in this instance
E8 contains the value to add to the date – 21 in this instance
“0101011” defines that the formula will ignore Tuesday, Thursday, Saturday and Sunday from the calculation (i.e. they are none work days)
A6:A8 references the cells used for “holiday dates”
In our example A6:A8 includes:
1/07/2018
2/07/2018
3/07/2018
By ignoring the non-working days indicated in “0101011” and the holiday dates, the result is 20/08/2017.
For simple excel based project plans this formula is great and allows simple plans to be built using working days.
Give it a try and let us know your thoughts in our comments section below.