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.
For all of the 5 rules. Each rule then looks at the different combinations of Start and End date, before adding the number of whole week working days to the remaining number of working 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.

 

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.

Article Feedback form
Did you find this article useful?

Version 1.01 – Last Update: 26/02/2021