Ageing Calculated Field excluding weekends using JMCF

[Marc Jason Mutuc]

I need to create a field that would capture the ageing of a ticket but would exclude the weekends. Basically, the ageing should only consider work/business days.

[David _old account_ · Accepted solution]

Hi,

you can create a calculated number field with the following formula:

<!-- @@Formula:
start = issue.get("created");
end = new Date();

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

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

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

// Adjust days to add on (w2) and days to subtract (w1) so that Saturday
// and Sunday are not included
if (w1 == Calendar.SUNDAY && w2 != Calendar.SATURDAY) {
    w1 = Calendar.MONDAY;
} else if (w1 == Calendar.SATURDAY && w2 != Calendar.SUNDAY) {
    w1 = Calendar.FRIDAY;
}

if (w2 == Calendar.SUNDAY) {
    w2 = Calendar.MONDAY;
} else if (w2 == Calendar.SATURDAY) {
    w2 = Calendar.FRIDAY;
}

return daysWithoutWeekendDays-w1+w2;
-->

Hope this helps,

David

[Mani Reddy]

@David _old account_ Hi David!!

Thanks for the post. Similarly would it be possible to have no.of days in particular status excluding Saturday and Sunday using calculated filed.

[David Fischer]

Hi Mani,

that's a good idea, but it's also a little more complicated. How should it work if the issue stays in a particular status only 3 hours? Is it 1 or 0 days? And what if the issue enters the status Friday at 5pm and leaves it Monday at 9am? How long did it stay in the status?

Basically, the problem with _work time durations_ in general is that they only make sense for a particular person, according to their own personal work schedule (which includes daily work schedule, PTO, Holidays, etc.)

[Mani Reddy]

@David Fischer @David _old account_  You are right.

We are not accounting PTO and Holidays for now as that will bring additional complexities in calculations. So even if there is a holiday or the developer is on PTO, we count it as long as it's not sat or Sunday. If the issue enters the status only for 3 hours we will consider it as 0. If the issue enters a status Friday at 5PM and leaves at Monday at 9 AM it is still consider as 0. If an issues enters a status at 9:AM on Tuesday and leaves the status at 9:00 AM Wednesday then it is considered as 1 day.

Thanks in advance. 

[David Fischer]

Unfortunately, JMCF currently doesn't offer a Time in Status custom field that supports that kind of worktime calculation. You could create your own Calculated Number field with some Groovy scripting (in JMCF 2 at least) and adapt the script above. To figure out the time spent in a particular status, you can use the issue history for the Status field - I think you'll find multiple examples of that on the Community.