How to calculate total time spent From To Do to Done

[Anonymous]

We have a requirement that we have some statuses like “To Do → In Progress → Awaiting on Dependency → In Review → Done”.  

We have to calculate the time spent from “To Do to Done”. 

Hence we do not have to calculate the time of “Awaiting on Dependency”.

Only we have to calculate the time “To Do → In Progress → In Review → Done”. 

[Damian Wodzinski · Accepted solution]

Hi,

I resolved similar problem, this will require third party addons. In my case, it was "ScriptRunner for Jira".

It would take some time, to develop the correct solution, but I will share my code with you. In this case, I had to skip time, during which ticket was in pending state, and also I had to skip counting, during weekends.

If you do not need to calculate weekends as I did, you can simply get the time, from creationd date till the resolving date. Then by looping through statuses changes, find how long it took to stay in "Awaiting.." status, then simply substract those two values.

import com.atlassian.jira.issue.Issue
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.changehistory.ChangeHistoryItem
import java.sql.Timestamp
import com.atlassian.jira.issue.changehistory.ChangeHistoryItem.Builder
import com.atlassian.jira.issue.history.ChangeItemBean
import java.util.Calendar
import java.time.*
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

def issueManager = ComponentAccessor.getIssueManager();
def changeHistoryManager = ComponentAccessor.getChangeHistoryManager();
long resultMillis = 0l;
def result = "";
List<ChangeItemBean> status = changeHistoryManager.getChangeItemsForField(issue, "status")
ChangeItemBean first = new ChangeItemBean("jira", "status", "created", "created", "Waiting for support", "Waiting for support", issue.getCreated())
status.add(0, first)

for(int i = 0; i < status.size(); i++){
def start = status[i].getCreated().toLocalDateTime();
//log.error(status[i].getToString() + " " + i)
//log.error(start)
if(!status[i].getToString().equals("Pending") && !status[i].getToString().equals("Closed") && !status[i].getToString().equals("Resolved")){
if(i != status.size()-1){
def end = status[i+1].getCreated().toLocalDateTime();
if( end.getDayOfWeek().equals( DayOfWeek.SATURDAY )) {
end = end.plusDays(2).toLocalDate().atStartOfDay().plusHours(8).plusMinutes(1);
}
if( end.getDayOfWeek().equals( DayOfWeek.SUNDAY )) {
end = end.plusDays(1).toLocalDate().atStartOfDay().plusHours(8).plusMinutes(1);
}
if( end.getHour() >= 22){
end = end.plusDays(1).toLocalDate().atStartOfDay().plusHours(8).plusMinutes(1);
}
if( end.getHour() < 8) { 
end = end.toLocalDate().atStartOfDay().plusHours(8).plusMinutes(1);
}
if( start.getDayOfWeek().equals( DayOfWeek.SATURDAY )) {
start = start.plusDays(2).toLocalDate().atStartOfDay().plusHours(8);
}
if( start.getDayOfWeek().equals( DayOfWeek.SUNDAY )) {
start = start.plusDays(1).toLocalDate().atStartOfDay().plusHours(8);
}
if( start.getHour() >= 22){
start = start.plusDays(1).toLocalDate().atStartOfDay().plusHours(8);
}
if( start.getHour() < 8) {
start = start.toLocalDate().atStartOfDay().plusHours(8);
}
log.error("Status: " + status[i].getToString() + " Created: " + start + " i: " + i)
log.error("End /(next status start/): " + end)
while(start.isBefore(end.minusDays(1))){
def last = start.toLocalDate().atStartOfDay().plusHours(22);
ZonedDateTime zdtStart = ZonedDateTime.of(start, ZoneId.systemDefault());
ZonedDateTime zdtLast = ZonedDateTime.of(last, ZoneId.systemDefault());
long starte = zdtStart.toInstant().toEpochMilli();
long laste = zdtLast.toInstant().toEpochMilli();
resultMillis = resultMillis + laste - starte
start = start.plusDays(1).toLocalDate().atStartOfDay().plusHours(8);
if( start.getDayOfWeek().equals( DayOfWeek.SATURDAY )) {
start = start.plusDays(2).toLocalDate().atStartOfDay().plusHours(8);
}
if( start.getDayOfWeek().equals( DayOfWeek.SUNDAY )) {
start = start.plusDays(1).toLocalDate().atStartOfDay().plusHours(8);
}
} 
ZonedDateTime zdtStart = ZonedDateTime.of(start, ZoneId.systemDefault());
ZonedDateTime zdtEnd = ZonedDateTime.of(end, ZoneId.systemDefault());
long starte = zdtStart.toInstant().toEpochMilli();
long ende = zdtEnd.toInstant().toEpochMilli();
resultMillis = resultMillis + ende - starte
} else {
// log.error(status[i].getToString())
if(!status[i].getToString().equals("Closed") && !status[i].getToString().equals("Resolved")){ 
if(!status[i].getToString().equals("Pending")){
ZonedDateTime zdtStart = ZonedDateTime.of(start, ZoneId.systemDefault());
long starte = zdtStart.toInstant().toEpochMilli();
resultMillis = resultMillis + System.currentTimeMillis() - starte
}
}
}
} 
}
long seconds = resultMillis.longValue() / 1000;
long minutes = seconds / 60;
long hours = minutes / 60;
long days = hours / 24;
String time = days + "d " + hours % 24 + "h " + minutes % 60 + "m " + seconds % 60 + "s" + result;
log.error(time)
return seconds;

[Damian Wodzinski]

This is "Scripted Field" for Jira server. But I think this is also possible in Jira Cloud.

[Rodolfo So]

Hi @Damian Wodzinski 

Can you provide screenshots of your scripted field and workflow status? Thanks

[Yuliia_Borivets__SaaSJet_]

Hi @Bret Justus 

Maybe this article with cycle/lead time will be helpful in this case:

3 tools to analyze Cycle and Lead Time in the Jira

Also, if you need to calculate time for some statuses, excluding others (“Awaiting on Dependency” in your case), check out Time in Status for Jira Cloud. You can create status groups to calculate time for certain statuses only.

My team has developed this ass-on. Let me know if you have any questions.

Kind regards

[Mehmet A _Bloompeak_]

Hi @Anonymous ,

There is a good solution for your request - Status Time app developed by our team.

It can display the time spent on specific statuses.

It provides reports on how much time passed in each status. It has grouping feature so that by grouping statuses(In Progress → In Review → Done) you can get total time. You can also export the report as CSV and open it in excel.

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(e.g. average in progress time by project, average cycle time by issue creation month). And all these are available as gadgets on the dashboard too.

Here is the online demo link, you can see it in action and try without installing the app. Hope it helps.

[Emre Toptancı _OBSS_]

Hello,

Our team at OBSS built Timepiece - Time in Status for Jira for this exact need and more. 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 and each assignee. 

 

It also has Duration Between Statuses report that shows the duration between two statuses. This report type is the one you are looking for.

 

You can calculate averages and sums of those durations grouped by the issue fields you select. For example total in progress time per customer (organization) or average resolution time per week, month, issuetype etc.

And the app has a REST API so you can get the reports from Jira UI or via REST.

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.

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

Timepiece - Time in Status for Jira

EmreT

[Rahul_RVS]

Hi @Anonymous 

If you would be interested in a third party app to group your statues and view time for each of your status groups, you can try out our app

Agile Tools - Epic Tree, Links Tree and Time in Status 

The add-on provides the time in each status for the entire lifecycle of the issue and you can filter by issue type as well. You can also combine your statuses to define your lead/cycle/resolution time and also extract the transitions history of the issues.