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

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 !

2 answers

2 accepted

2 votes
Accepted answer

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

Hi David,

Thank you this works perfectly !

@David Fischer [Innovalog] 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

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.

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..

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.

@JiraConfAdmin do you have something like that?

@David Fischer [Innovalog] 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 :(

0 votes
Accepted answer

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

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...

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.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 29, 2018 in Marketplace Apps

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,408 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you