A recent tip of mine showed how to calculate the number of working days there are between two dates. Also where there are bank holidays involved. Today I shall investigate the function “NETWORKDAYS.INTL” as this function allows you to have different days for the weekends. i.e. not Saturday and Sunday.
Here is a simple set sheet for this calculation. In a new cell away from this list use the Insert Function Tool to locate and use the NETWORKDAYS.INTL formula. This is the dialog box that will appear.
- To start, select the Start Date cell and then the End date cell.
- Select the OK button to see the result so far.
- Staying in the result cell Select the
On the Formula Bar and the same dialog box for the formula arguments appears again.
- Click in the third line which is the Weekends line to activate this part.
To use different days for your weekend calculation, refer to this list that denotes which number to use for your particular requirements.
- Type in the number you require in this third line of the dialog box.
- If you have any holidays taken or booked for the future type these dates into the spreadsheet and select them for the fourth line.
Your filled Dialog box will look like this.
- Select the OK button and your result will be like this.
Now change the weekend days to Tuesdays Wednesdays. This changes the answer. I selected a holiday which falls on my weekend day.
Start using NETWORKDAYS.INTL
You can see that if you don’t remember which days you take for a weekend or non-working day, even if you chose one of those for a day off you won’t actually be taking it off twice in the calculation, therefore not lose a day’s holiday. I hope this tip helps you keep tabs on how many days you work and are paid for accordingly.