Exclude non-working hours and weekends in time difference between statuses

James Wardle August 13, 2021

We're trying to decipher the time difference between when a ticket is set to In Progress and when it is set to Done.

Currently we have a custom field that sets the date/time for In Progress when the ticket is set to that, and a custom field that sets the date/time for Done when the ticket is set to that. There is then another custom field that works out the difference between the two date stamps (worked out as {{issue.customfield_10267.diff(issue.customfield_10266).hours}}).

All of the above using automation rules.

However we have two problems:

a) the time difference doesn't exclude non-working hours and weekends. Is there anyway to set it to just show working hours?

b) we have members of a team across different time zones. They all work 09:00-18:00. If it is possible to set working hours, would the data be skewed by the timezones?

Hopefully that explains it but any help would be appreciated.

 

4 answers

2 votes
Ravi Sagar _Sparxsys_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 13, 2021

Hi @James Wardle 

Try this and let me know if it works.

 {{issue.customfield_10267.diff(issue.customfield_10266).businessDays}}

This will give you days not hours but if you look at this page you will get more ideas: https://support.atlassian.com/jira-software-cloud/docs/smart-values-date-and-time-functions/

For timezone I expect this to still work but test it for users from other time zones as well.

I hope it helps.

Ravi 

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 13, 2021

Hi @James Wardle 

Yes, and...to what @Ravi Sagar _Sparxsys_ suggests:

  • Please check with your site admin for the time tracking settings of working days and hours.  See how those align with your understanding of those values.
  • You may want to consider adjusting the time zone of issues (perhaps to the assignee's time zone) prior to calculating the time differences.  I found those relevant even when the team is in a single-location. For example:

{{issue.customfield_10267.convertToTimeZone(issue.assignee.timeZone).diff(issue.customfield_10266.convertToTimeZone(issue.assignee.timeZone)).businessDays}}

Best regards,
Bill

Like # people like this
1 vote
Yuliia_Borivets__SaaSJet_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
September 16, 2021

Hi @James Wardle 

If you're still looking for a solution, check out Time Between Statuses. My team has developed this app to monitor the execution time between statuses in the workflow (e.g. from "in progress" to "done"). Here you can set time limits to highlight overdue issues.

864ca30d-f4c3-4bc0-a6d0-3aef4050a81a.jpeg

You can schedule working days, time zone, working hours, breaks, and holidays.

2021-09-16_9-47-22.jpg

Let me know if you have any questions.

James Wardle September 22, 2021
0 votes
Emre Toptancı [OBSS]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
September 8, 2021

Hello @James Wardle ,

Our team at OBSS built Time in Status app for this exact need. It has built-in business calendar support and it is available for Jira Server, Cloud, and Data Center.

Time in Status allows you to see how much time each issue spent on each status or assigned to each assignee. You can also combine statuses into consolidated columns to see metrics like Ticket Age, Cycle Time, or Lead Time. 

You can calculate averages and sums of those durations grouped by issue fields you select. 

tisCloud_StatusDuration_LeadTime_with Estimates.png  tisCloud_StatusDuration_LeadTime_Average.png

tisCloud_StatusDuration_LeadTime_Average_TimeGrouped.png  tisCloud_StatusDuration_LeadTime_Chart.png

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well.

As I stated before, the app has custom calendar support. For each report type you can define your own calendars with your own working days, working hours, holidays and timezone.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

Using Time in Status you can:

  • See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
  • Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issue type.)
  • Export your data as XLS, XLSX, or CSV.
  • Access data via REST API. (for integrations)
  • Visualize data with various chart types.
  • See Time in Status reports on Jira Dashboard gadgets

https://marketplace.atlassian.com/apps/1211756/

EmreT

James Wardle September 22, 2021
0 votes
Bloompeak Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
September 1, 2021

Hi @James Wardle ,

For this specific need, we developed Status Time app. It provides reports on how much time passed in each status excluding non-working hours.

Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days. It has various other reports like assignee time, status entry dates, average/sum reports by any field(eg. average in progress time by project, average cycle time by issue creation month). And all these are available as gadgets on the dashboard too.

WorkingHoursAndHolidays.png

Regarding your second question about having members in different time zone, I think that you should extend your working calendar to the union of these time zones. That is, if you have members in Germany and US with working hours starting  at 9 AM to 18 PM in their own time zones respectively, you should create a working calendar which starts 9AM Germany Time and ends at 18 PM US time as in below screenshot. Because these are time ranges that your company is open and handling issues.

UnionOfMembersWorkingSchedule.png

James Wardle September 22, 2021

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events