JIRA Misc Custom Fields date interval calculation ignoring weekends?

[Jordan Pflum]

Hello there!

Using JIRA Misc Custom Fields, I have been able to create a "Calculated Number Field" which ultimately returns the number of days in a range of two dates using the following syntax:

<!-- @@Formula: (issue.get("customfield_11618")==null ? null : (issue.get("customfield_11618").getTime() - issue.get("customfield_11616").getTime()) / 1000 / 3600 / 24) -->

The particular custom field IDs in this case are custom fields of the type date picker....... these are the dates for which the range between is returned.

I would very much like to IGNORE WEEKENDS in this return if this is possible via the HTML calculation? So say for example the the two dates in question are 01/02/2014 and 01/06/2014. I would like for this range to ignore the weekends, and return "2" rather than "4".

Is this possible, and, if so, does anyone know how to write that command?

[Jordan Pflum · Accepted solution]

OK, thanks very much for your help! With a few modifications, I was able to get your original suggestion to work perfectly for my need:

<!-- @@Formula:long days(Date start, Date end) {
//Ignore argument check

Calendar c1 = GregorianCalendar.getInstance();
c1.setTime(start);
int w1 = c1.get(Calendar.DAY_OF_WEEK);
c1.add(Calendar.DAY_OF_WEEK, -w1 + 1);

Calendar c2 = GregorianCalendar.getInstance();
c2.setTime(end);
int w2 = c2.get(Calendar.DAY_OF_WEEK);
c2.add(Calendar.DAY_OF_WEEK, -w2 + 1);

//end Saturday to start Saturday
long days = (c2.getTimeInMillis()-c1.getTimeInMillis())/(1000*60*60*24);
long daysWithoutSunday = days-(days*2/7);

if (w1 == Calendar.SUNDAY) {
w1 = Calendar.FRIDAY-5;
}
if (w2 == Calendar.SUNDAY) {
w2 = Calendar.FRIDAY-5;
}
if (w1 == Calendar.SATURDAY) {
w1 = Calendar.FRIDAY;
}
if (w2 == Calendar.SATURDAY) {
w2 = Calendar.FRIDAY;
}
return daysWithoutSunday-w1+w2;
}
if (issue.get("customfield_11615")==null)
return null;
if (issue.get("customfield_11617")==null)
return days(issue.get("customfield_11615"), new Date());
return days(issue.get("customfield_11615"), issue.get("customfield_11617"))
-->

[DovelAdmin]

Jordan!  Hello!  Thank you for working everything up to this.  I swapped the customf fields with created and resolutiondate and it  indeed calculates the difference properly to exclude the weekend!

Quesition: Is there a way to out put the value in an Integer or 2 or 3-digit number?  For example, I see that the numbers are geting rounded up 2.49 = 2 and 2.50 = 3.  Any way to show 2 more digits so we can tell maybe an issue took 4.5 days to close?

[Piyush_Annadate]

any ways to get it rounded-off?

[David _old account_]

You can use something like the following formula:

<!-- @@Formula:long days(Date start, Date end) {
    //Ignore argument check

    Calendar c1 = GregorianCalendar.getInstance();
    c1.setTime(start);
    int w1 = c1.get(Calendar.DAY_OF_WEEK);
    c1.add(Calendar.DAY_OF_WEEK, -w1 + 1);

    Calendar c2 = GregorianCalendar.getInstance();
    c2.setTime(end);
    int w2 = c2.get(Calendar.DAY_OF_WEEK);
    c2.add(Calendar.DAY_OF_WEEK, -w2 + 1);

    //end Saturday to start Saturday 
    long days = (c2.getTimeInMillis()-c1.getTimeInMillis())/(1000*60*60*24);
    long daysWithoutSunday = days-(days*2/7);

    if (w1 == Calendar.SUNDAY) {
        w1 = Calendar.MONDAY;
    }
    if (w2 == Calendar.SUNDAY) {
        w2 = Calendar.MONDAY;
    }
    return daysWithoutSunday-w1+w2;
}
if (issue.get("customfield_11618")==null || issue.get("customfield_11616")==null )
  return null;
 
 return days(issue.get("customfield_11616"), issue.get("customfield_11618"))
-->

[Jordan Pflum]

Hi David,

Thanks very much for this suggestion! This is very helpful!

Only one question remains for me though........ this method works great in general, but I noticed in testing it out that both Sundays and Mondays are being discounted, rather than Saturdays and Sundays.

I tried changing that reference in lines 18/19 and in 21/22 to refer to Saturdays and Sundays, but this caused the calculation if one of the dates is a Saturday to be very wrong, while the scenario of one of the dates in question being a Sunday worked out OK...........

[David _old account_]

To be honest, I don't quite get this function, which I found on the Internet. Maybe you can try the function at http://stackoverflow.com/questions/4600034/calculate-number-of-weekdays-between-two-dates-in-java

Just remove "public static" in front of the function definition.

[Reinaldo Arroyo]

how can I excluded Saturday and sunday ?

you can help me.

Fecha de Referido     --  Date Picker                  Customfield ID = 16310

Fecha de Vencimiento  --  Calculated Date/Time Field   Customfield ID = 16311