Sum "Resolved" date field and a number of days custom field

[Camille Lecerf]

Hi, 

I would like to set a date custom field.
For that field, I wan't to add a time-period (number of days) to the resolution date.

I tried with :
- Scriptrunner
- Calculated Date/Time Field from add-on Jira Misc Custom Fields

I don't manage to do it (I'm a beginner in coding)

Could someone help me please ? Thank you !

[David Fischer · Accepted solution]

Hi Camille,

using JMCF, and assuming the custom field (called "Number field" below) that contains the number of days is a Number custom field (if it's a text or select field, the code would be slightly different), you can use the following code:

<!-- @@Formula:
if (issue.get("resolutiondate") == null || issue.get("Number field") == null)
  return issue.get("resolutiondate");
long ms = issue.get("resolutiondate").getTime() + 24*60*60*1000*issue.get("Number field");
return new Date(ms);
-->

 This code will return:

• null if the resolution date is null (the issue is unresolved)
• the resolution date is the number field is null (empty)
• the resolution date + the number of days in the number field otherwise

David

[Camille Lecerf]

Hi David,

Thank you this works perfectly !

[Ricky Lin]

@David Fischer this is a great and simple way to get thedata.  I was able to apply this to my custom date. 

Question: What can we do to exclude weekends with this?  I've tried using part of the code below (IN BOLD) to exclude it but doesn't seem to work:

<!-- @@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("created")==null)
return null;
if (issue.get("resolutiondate")==null)
return days(issue.get("created"), new Date());
return days(issue.get("created"), issue.get("resolutiondate"))
-->

 Would be nice to get some insight and it's got quite a lot of business use from what I can tell

[David Fischer]

If you look in your atlassian-jira.log logfile, you'll see that you have at least one syntax error on that script: you're missing a semicolon on the last line.

[Ricky Lin]

Hey David.  Thanks for the hint.... That's actually not the entirety of the script.  Below is a more accurate representation:

<!-- @@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;
}

//customfield_11209 = Original Estimate Date
//customfield_12302/12303/12304 = Number Fields 1/2/3

if (issue.get("customfield_11209") == null || issue.get("customfield_12302") == null || issue.get("customfield_12303") == null || issue.get("customfield_12304") == null)
 return issue.get("customfield_11209");
long ms = issue.get("customfield_11209").getTime() + 24*60*60*1000*issue.get("customfield_12302") + 24*60*60*1000*issue.get("customfield_12303") + 24*60*60*1000*issue.get("customfield_12304");
return new Date(ms);
-->

I now properly get the Original Estimate Date + the sum of the 3 number fields, but I cannot seem to get it to interact with the calendar part to output it correctly.  If you have more hints to give out, i'd love to take a jab at it.  Many thanks..

[David Fischer]

What you're trying to do is not what the "days" function was meant for. The days function calculates the number of working days between two dates. What you're trying to do is to start from a date and add a certain number of work days to it. For that, you'll need a different function.

@Radhika Vijji do you have something like that?

[Ricky Lin]

@David Fischer thank you for wording it much better than I can....  You're exactly right with my intention.  I can see it having a very common business case as I see multiple similar questions asked on Stackoverflow - I'm unfortunately not a strong coder at all to put the pieces together :(

[Alexey Matveev · Accepted solution]

Hello,

If you are on Jira Server/Data Center you could use Power Custom Fields add-on. It is a free add-on. You could create a custom field with the following code

return %key%.resolutionDate + "3d";

In this case you would have a custom field which would add 3 days to resolution date. You can find more info here:

https://marketplace.atlassian.com/plugins/com.keplerrominfo.jira.plugins.keplercf/server/overview

[Camille Lecerf]

Hello Alexey, thank you for your answer !

In this add-on would it be possible to use the number of days of my custom field instead of fixing it ? In this example it is fixed to 3 days

If it is possible, I would prefer to use Scriptrunner or Calculated Date/Time Field from add-on Jira Misc Custom Fields in order to avoid installing a new add-on...

[Alexey Matveev]

Power Script is easier for coding. You do not need to know internals of Jira. I do not work with Jira Misc Custom Fields. For Scriptrunner it would be like this.

import com.atlassian.jira.component.ComponentAccessor
import java.sql.Date
import java.sql.Timestamp
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.issue.ModifiedValue
def issue = ComponentAccessor.getIssueManager().getIssueByCurrentKey("issuekey")
def csDate2 = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("test_date2")

Timestamp csDate1Value = issue.getResolutionDate()
Date csNewDateValue = new Date(csDate1Value.getTime() + 15*24*60*60*1000);
csDate2.updateValue(null, issue, new ModifiedValue("", (Object) csNewDateValue), new DefaultIssueChangeHolder())

 This line 15*24*60*60*1000 means 15 days (first multiplier). I did not check the code. There can be typos. 

You can see the difference in the code simplicity between Power Scripts and Adaptivist Scriptrunner. But it is up to you what to use.