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

[Josh]

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

[Payne · Accepted solution]

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);
​

[Josh]

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

[Gireesha Jeera]

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.