If you would like to know how many working days have passed since a Case changed Status, for example, then we have found a formula that will help you do this.

In this example, the report consists of all Audit Records where the Status field has changed. The formula compares the Created At date of those Audit Records (audit_records.fields.created_at) to today's date (curdate()).

5 * (DATEDIFF(curdate(), audit_records.fields.created_at) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(audit_records.fields.created_at) + WEEKDAY(curdate()) + 1, 1)

This formula looks complicated, but works very well. It works out how many full weeks there are between the two dates, and multiplies by 5 to get the number of working days in those weeks. It then uses the long string of numbers to work out how many more working days to add on based on which day of the week each of the dates lands on.

This formula does not take holidays into consideration. To be able to do this, you will have to hard code the holiday dates into the formula. For this year, the formula looks like this:

(5 * (DATEDIFF(curdate(), audit_records.fields.created_at) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(audit_records.fields.created_at) + WEEKDAY(curdate()) + 1, 1))
-
(IF('2016-01-01' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) + 
IF('2016-03-17' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) +
IF('2016-03-25' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) +
IF('2016-03-28' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) +
IF('2016-05-02' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) +
IF('2016-05-30' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) +
IF('2016-07-12' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) +
IF('2016-08-29' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) + 
IF('2016-12-26' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) + 
IF('2016-12-27' BETWEEN audit_records.fields.created_at AND curdate(), 1, 0) )

This subtracts a 1 from the result of the original formula for every holiday that it finds in between the 2 dates.

You can amend audit_records.fields.created_at and curdate() to be fields relevant to your own report.