Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,299,890
Community Members
 
Community Events
165
Community Groups

How to calculate number of days between two system fields excluding the weekends.

I am utilizing a post-function script through the Adaptavist Scriptrunner plugin for Jira that is setting the Original Estimate and Remaining Estimate Time-Tracking fields.  The script (posted below) is using the System Fields, Created Date and Due Date, to find the difference between the dates in number of days.  The script is working properly, and setting the appropriate fields, but there is a bug:  it is including weekends in the calculation.  I have yet to find an appropriate solution that excludes Saturday's and Sunday's from this calculation, and am wondering if anybody else has had a similar issue and has found a solution.  Thanks in advance.

 

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.issue.Issue;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.issue.CustomFieldManager;
import com.atlassian.jira.issue.fields.CustomField;
import com.atlassian.jira.issue.IssueManager;
import com.atlassian.jira.user.util.UserManager;
import com.atlassian.jira.issue.MutableIssue
import java.util.Date.*

def createdDate = issue.getCreated()
log.warn("Date Created: " + createdDate)
def dueDate = issue.getDueDate()
log.warn("Due Date: " + dueDate)
def numberOfDays = dueDate - createdDate
log.warn("Number of Days Between Dates: " + numberOfDays)

issue.setOriginalEstimate(numberOfDays * 8 * 60 * 60); //Set Original Estimate to number of days between Due Date and Created Date
issue.setEstimate(numberOfDays * 8 * 60 * 60); //Set Remaining Estimate to number of days between Due Date and Created Date

1 answer

1 accepted

0 votes
Answer accepted

This seems to get the job done. Found at https://stackoverflow.com/questions/18354727/how-to-calculate-number-of-days-between-two-dates-excluding-weekend-java and added necessary imports. You could take this and make a function out of it.

import java.text.DateFormat;
import java.text.SimpleDateFormat;

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date date1 = df.parse("01/05/2019");
Date date2 = df.parse("31/05/2019");
Calendar cal1 = Calendar.getInstance();
Calendar cal2 = Calendar.getInstance();
cal1.setTime(date1);
cal2.setTime(date2);

int numberOfDays = 0;
while (cal1.before(cal2)) {
if ((Calendar.SATURDAY != cal1.get(Calendar.DAY_OF_WEEK))
&&(Calendar.SUNDAY != cal1.get(Calendar.DAY_OF_WEEK))) {
numberOfDays++;
}
cal1.add(Calendar.DATE,1);
}
System.out.println(numberOfDays);

Payne,

This solved the problem perfectly.  Thank you for your help.  For anyone who is attempting to achieve the same result in their Jira instance, here is how I modified the above information to work in the workflows:

 

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.issue.Issue;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.issue.CustomFieldManager;
import com.atlassian.jira.issue.fields.CustomField;
import com.atlassian.jira.issue.IssueManager;
import com.atlassian.jira.user.util.UserManager;
import com.atlassian.jira.issue.MutableIssue
import java.util.Date.*
import java.text.DateFormat;
import java.text.SimpleDateFormat;

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
def createdDate = issue.getCreated()
log.warn("Date Created: " + createdDate)
def dueDate = issue.getDueDate()
log.warn("Due Date: " + dueDate)

Calendar cal1 = Calendar.getInstance();
Calendar cal2 = Calendar.getInstance();
cal1.setTime(createdDate);
cal2.setTime(dueDate);

def numberOfDays = 0;
while (cal1.before(cal2)) {
if ((Calendar.SATURDAY != cal1.get(Calendar.DAY_OF_WEEK)) && (Calendar.SUNDAY != cal1.get(Calendar.DAY_OF_WEEK))) {
numberOfDays++;
}
cal1.add(Calendar.DATE,1);
}

log.warn("Number of Days Between Dates: " + numberOfDays)

issue.setOriginalEstimate(numberOfDays * 8 * 60 * 60); //Set Original Estimate to number of days between Due Date and Created Date
issue.setEstimate(numberOfDays * 8 * 60 * 60); //Set Remaining Estimate to number of days between Due Date and Created Date

Like Payne likes this

Hi, I want to use the similar query as below for SQL, can you please help here?

I am trying to display duration column with difference between 2 dates (From Date and To Date) in Confluence. I have tried below:

select *,(CAST(ROUND(ceiling(DATEDIFF(DAY,'From Date','To Date')))as int))+1+ " days" as 'Duration' from T1

this gives the result, however given the dates 25-Jun-2020 to 26-Jun-2020 - duration displayed as 1 instead of 2.

your help is appreciated here. 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

328 views 2 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you