Type: Function
Function: TIMELINE_BUSINESS_DAYS
Syntax: TIMELINE_BUSINESS_DAYS(timeline_field, event|event_list, [timezone="UTC"], [hour_start="09:00"], [hour_end="17:00"], [weekday_start=1], [weekday_end=5], [closed_only=false], [ignore_skipped=false])
Description: Returns a decimal number representing the business days spent in a given (list of) timeline event(s). Event matching is case-insensitive. This operator combines the timeline iteration logic of TIMELINE_DURATION with the business duration calculation of BUSINESS_DAYS.
Partial business days are calculated relative to the working hours (from 09:00 to 17:00 by default), not based on the 24-hour clock day. For instance, if an item spent 4 hours in In Progress during working hours, the result will be 0.5 (4 hours out of an 8-hour business day).
If you change the working hours to be from 06:00 till 22:00 (16-hour day), then 4 hours become 0.25 of a business day.
The range of days worked within the week is specified using the ISO8601 standard, where Monday is 1 and Sunday is 7.
Timeline events can be open or closed. An event is open when the item is currently in that state (e.g. the issue is currently in the workflow status In Progress). The operator uses NOW() as an end_at for open events. To ignore open events and only calculate realized durations, set closed_only to true.
If no events match the provided list of events then the operator returns 0. This impacts the calculation of averages as the value (0) will be counted and artificially lower the resulting average. If you prefer to restrict the average to actual values, set ignore_skipped to true. When this mode is enabled the operator returns NULL instead of 0 when no events match.
The list of supported timezones is specified below.
Sample usage:
TIMELINE_BUSINESS_DAYS(workflow_timeline, "In Progress")returns the business days spent in the workflow statusIn Progress. For items currently inIn Progress, the duration will be continuous (usingNOW()as end).TIMELINE_BUSINESS_DAYS(workflow_timeline, ["In Progress", "Staging"])returns the combined business days spent inIn ProgressandStaging.TIMELINE_BUSINESS_DAYS(workflow_timeline, "In Progress", "CET")returns the business days spent inIn Progress, with working hours evaluated in theCETtimezone.TIMELINE_BUSINESS_DAYS(workflow_timeline, "In Progress", "CET", "10:00", "16:00", 2, 4)returns the business days spent inIn Progress, with a custom schedule of Tuesday to Thursday, 10:00 to 16:00 CET.TIMELINE_BUSINESS_DAYS(workflow_timeline, "In Progress", "UTC", "09:00", "17:00", 1, 5, true)returns the closed business days spent inIn Progress. For items currently inIn Progress, the result will be zero since the event is open and the closed only mode is requested.TIMELINE_BUSINESS_DAYS(workflow_timeline, "In Progress", "UTC", "09:00", "17:00", 1, 5, false, true)returns the business days spent inIn Progress. If no events match then the result will beNULLsince the ignore skipped mode is requested.
List of supported timezones:
Below is the list of timezone abbreviations supported by the timezone argument: ACST, ADT, AEST, AKDT, AST, AWST, BST, CAT, CDT, CEST, CET, CST, ChST, EAT, EDT, EEST, EET, EST, GMT, HDT, HKT, HST, IDT, IST, JST, KST, MDT, MSK, MST, NDT, NZST, PDT, PKT, PST, SAST, SST, UTC, WAT, WEST, WIB, WIT, WITA
For a complete list of all supported "named" timezones (e.g. Europe/Amsterdam), see the following help article.