Advanced Insights - How can I calculate working days?

Like many SQL based reporting tools, when counting time between two dates the AIRB defaults to calendar days. But users wish for it to be displayed in working days. This can be achieved using the Calculated Field functionality.

Here’s the steps how:

1. Add the start and end dates into your report. In this example, it is Job Live Date and Job Closing Date:

2. First, you need to work out the time in days between the two dates and simultaneously convert it to a pure number (as the original values contain numbers and text to form a date and timestamp).  The pre-defined calculation ‘’Days between, SQL server” will do this.  

In values, select your start and end date. Video 6 covers creating a calculation if you are unfamiliar.

3. Once created you will find your calculated field in the red folder in the left-hand list. Drag this field into your report.

4. Now create a second calculated field. This time keep the Formula Type set to Simple. Use the input tools as follows:

1. Click Opening Bracket
2. Select field “Calc 1” (or whatever you’ve called your first calculated field)
3. Click Divide
4. Type 7 and click Add
5. Click Closing Bracket
6. Click Multiply
7. Type 5 and click Add
8. Click Validate followed by Save

5. Once created you will find your calculated field in the red folder in the left-hand list. Drag this field into your report.