Working Days Range for Weekends
Working Days Range for Weekends
When working with dates we can use the expression daysBetween to determine how many whole days there are between 2 specific dates. While this is very useful as a starting point it may not be exactly what we need for our use case.
For example we need to determine the number of working days there are from a given date to an end date. For this example we are going to determine that a working day are between Monday to Friday.
We will need to calculate the number of full weeks in the date range, of which each week will contain 5 working days. We will also need to calculate the number of working days in any days that are not full weeks.
The knowledge map that we are using only contains four Concepts:
- A period of time which is the subject for the Relationships and Rules.
- A start and end date, which are both date concepts.
- working Days, our outcome, which is a number concept.
Figure 1: Basic Map with Start-, End date and Days as a result
There are 5 rules on the ‘has working days’ relationship to calculate the number of working days. Those rules do the following:
- Calculate the number of days in the period.
- Calculate the number of whole weeks in the period.
- Calculate the remaining number of days.
We can use the DaysBetween function to determine the total number of days for the period. We will need to add 1 to this number however so as to include the start day in the calculation.
Figure 2: First part calculating the total number of %DAYS
The new variable of the calculation is %DAYS (outcome of the daysBetween calculation), which is used in the next expression.
We can use the variable of the total number of days in the period to determine how many weeks there are. This can be done by dividing the value %DAYS by 7. By including the floor function we can get the number rounded down to the nearest whole number.
For example if we have 9 days in total we can divide 9 by 7 to get 1.285. By using the floor function we can round this down to the nearest whole number which is 1. This means that there is 1 full week in the total number of days.
This new value is the number of %WEEKS (outcome of the floor calculation), which is multiplied by 5 in the following expression – as there are 5 working days in a week.
Figure 3: second part is calculating the number of whole weeks
The new variable of the calculation is %WHOLE_WEEKS, which is used in the next expression.
To calculate the remaining days we can use the modulus function, this will give us the remainder that was rounded down when working out the number of weeks. The modulus function works by displaying the remainder that is left after dividing 2 numbers.
Using our example of 9 total days we are able to determine that the modulus of 9/2 is 2, meaning that the remaining number of whole days after divided into whole weeks is 2. 
Figure 4: third part is calculating the remainder after the whole weeks
We now need to determine whether or not the remaining days are working days or weekends. We can do this by determining what day the start day fell on.
We are able to use the dayOfWeek function to express the start of the period as a day of the week ranging from 1-7.
Figure 5: fourth part is returning the day of the week as number
As the day of the week is expressed as a number we can use it in combination with the remainder number of days to calculate how many are working days and how many are weekends.
If the start day is a Sunday (%NUMO = 7) then we use the following calculations.
If the remaining days added to the day value is equal to 7, this means that there are no remaining days and therefore we can add the number of days in the full weeks to the remaining number of days.
Figure 10: If Start date Sunday, add weeks and remaining days
This Rule differs in the last 3 conditions from the ‘equal to 7’ rule and calculates the remaining days, when the value of the start day and remaining days is greater than 7 we will need to take 1 away from the calculation. This is because the remaining days will contain a Sunday which we need to remove.
Figure 7: If Start date and remaining days is greater than 7
This Rule differs in the last 2 conditions calculating the remaining days, for any start day other than Sunday.
If the value of the start day and remains days is less than or equal to 6, you are able to use the calculation without subtracting a number. This is because there will be no weekends to subtract.
Figure 8: If Start date and remaining days is greater than 6
This Rule differs in the last 2 conditions calculating the remaining days, for a single weekend day within the week.
If the value is equal to 7 then you will need to subtract 1 as there is a weekend day that needs to be removed from the calculation.
Figure 9: If Start date and remaining days is greater than 7
This Rule differs in the last 2 conditions calculating the remaining days, if both weekend day are included.
If the value is greater than 7 then you will need to subtract 2 from the calculation as there is both a Saturday and a Sunday in the remaining days that need to be removed.
Figure 9: If Start date and remaining days is greater than 7
Note: The above map does not take bank holidays into consideration.
Using countRelationshipInstances with Bank Holidays from an API call between the date range can be deducted from the overall result.
Click on the ‘Export.rbird’ button to download the ‘Working Days Range with Weekends’ map used in this example. The knowledge map can then be imported into your Rainbird Studio
Query and Results
The main query is built on the rule ‘has working days’. The outcome of the query will be the number of working days between the two dates.