Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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
Payne Community Leader May 24, 2019

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 Apps & Integrations

🍻🍂Apptoberfest Update: Upcoming Virtual Events 🎉

Hello Community! I hope you've been enjoying the 🍂Apptoberfestivities🍂 (I know I have!) The event is heating up next week with a series of virtual events that we're calling the 🍻🍂Partner App ...

326 views 3 14
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