A couple of years ago I wrote about the NETWORKDAYS function which calculates the difference between 2 dates but also takes account of weekends and optional holidays (i.e. non-working weekdays). If you haven’t seen the tutorial, the link is here.
The NETWORKDAYS function assumes weekdays are Monday-Friday, but in this increasingly global world of ours, what if you are working on a project where this is not the case?
In the example below, I am working on a new IT system which will go live on 1st September (yes I know the date has passed – it’s an example!). Today’s date is June 1st 2016. The project team work a 6-day week, only taking Saturday off (that could be due to the workload or it could be due to the geographic location of the project).
This is where the NETWORKDAYS.INTL function comes in useful…
The formula in B5 is =Networkdays.INTL(B2,B3,17)
B2 is the start date
B3 is the end date
17 is the code indicating that the weekend is Saturday only
The full list of codes is:
| 1 (or omitted): | Saturday/Sunday |
| 2: | Sunday/Monday |
| 3: | Monday/Tuesday |
| 4: | Tuesday/Wednesday |
| 5: | Wednesday/Thursday |
| 6: | Thursday/Friday |
| 7: | Friday/Saturday |
| 11: | Sunday only |
| 12: | Monday only |
| 13: | Tuesday only |
| 14: | Wednesday only |
| 15: | Thursday only |
| 16: | Friday only |
| 17: | Saturday only |