Advanced Insights - How can I calculate working days?

Modified on Fri, 13 May, 2022 at 4:48 PM

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:

 

Graphical user interface, text, application, email

Description automatically generated

 

 

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.


Graphical user interface, text, application, email

Description automatically generated

 

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

Graphical user interface, text, application, email

Description automatically generated

 

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

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article