calculate the date and time with two custom field

Hi  ,

our requirement is to find the difference between the two date time custom fields .is it possible ??

if possible please provide me the solution asap .

i have installed JIRA misc custom fields and i tried with the below expression .But that is not working for me .

 

Examole : Field 1 :created date bs (customfield_19700)

Field 2 : Closed Date bs  (customfield_19716)

i need to find how the time taken to close the issue from the created date field .

these are JIRA custom field date time picker .

and also if it is possible with created and resolved date field 

 

 

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

 

4 answers

1 accepted

2 votes
Accepted answer

I assume you created a Calculated Number field, correct?

There is a small error in your formula, you need to check both fields for null values:

&lt;!-- @@Formula: 
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24; --&gt;

And of course you could also use the created or resolved fields, see https://innovalog.atlassian.net/wiki/display/KB/Using+issue.get%28%3Cfield_name%3E%29+in+scripts for details.

Hi david ,

i accept this .

But i have a small query ..

i checked with the field but it does not showing any values .

fyr 

i attached the screen shots ..

 

Field Type :Calculated Date time field 

search Template : date range picker 

<!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24; -->

filter.pngdate.png

But the field does not appearing in the screen 

i have mapped the field in the screen .

Please help me on this ..

 

As I already said, it needs to be a calculated NUMBER field. You are returning a number (the number of days between two dates), not a Date.

Yah its working fine .

Is it possible to take if the hours is 0 then need to show the minutes .

shall i do it by using the below script .

<!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24 / 60  ; -->

And I missed to say Thanks for the valuable  immediate support .

@David,

please help on this .we need to calculate the time between the two fields .

 

Well, the problem is, how are you going to know whether it's days, hours or minutes you're returning?

If you only return minutes, then your formula should be:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 60  ; --&gt;

If you want to return hours, you should further divide by 60.

And if you instead want to return a string describing the difference, then you'll need to create a Calculated Text field instead:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
diff = issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime() / 1000 / 60;
if (diff &lt; 60)
  return diff + " minutes";
diff = diff / 60;
if (diff &lt; 24)
  return diff + " hours";
return (diff / 24) + " days";
--&gt;

Hi David ,

i have some other questions also .

is it possible to calculate the time based on business working hours .

For example :i need to take the time between 9 am to 6 pm and weekdays from monday to friday only .

if created date or time is non working then i need to calculate only the working hours time only .

if u are not understanding my questions please let me know .

 

I think someone posted a solution to this some time ago. Search the answers, because it is fairly complicated.

David

Thanks David .

I hope i can get the answers . if i need any other helps , then i will ask here ..

Thanks for ur support .

Hi David ,

i created the issue at the same time i resolved .the custom field have the values .but it results something different .can you please help on this .

but it resilts with 1025718807 days

 

i used to created the Calculated Textfield  .


script i used :

 

<!-- @@Formula:
if (issue.get("customfield_19736")==null || issue.get("customfield_19735") == null)
return null;
diff = issue.get("customfield_19736").getTime() - issue.get("customfield_19735").getTime() / 1000 / 60 / 60;
if (diff < 60)
return diff + " minutes";
diff = diff / 60;
if (diff < 24)
return diff + " hours";
return (diff / 24) + " days";
-->

i am not sure what logic is used here ..

If possible explain then i can manage it own for improved tasks .

 

Sorry, I forgot parentheses in the "diff" calculation:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
diff = (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 60;
if (diff &lt; 60)
  return diff + " minutes";
diff = diff / 60;
if (diff &lt; 24)
  return diff + " hours";
return (diff / 24) + " days";
--&gt;

yah thanks ..

I used that and its working for me .

I have another questions .

Is it possible to filter the datas in jquery for those custom fields .

i have to take the report for ageing analysis .

so i have to take the report which are resolved less than 2 hrs , less than 1 day , more than 3 days , more than 7 days . is it possible to do with using jql filters . 

You'll need to use your first calculated number field for that - not easy to do comparisons with text. But easy to do with a number of hours for example (7 days = 7x24 hours...)

Don't forget to re-index JIRA after creating your calculated fields.

Hi , is these fields will display for the JIRA charts .

i have checked with pie charts  but it is not showing .

can u please let me know if it is possible .

David ,

in the last thing you have said that it is possible with do with hours .. but i am not sure .how can i do that . can u give me the detailed explanation .

 

 

You cannot use Calculated Number or Date Fields in most JIRA charts (because of a limitation in JIRA), only Calculated Text Fields.

As for the hours, just create a "HoursSpent" Calculated Number Field with the following formula:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600  ; --&gt;

which you can then use in JQL ( "HoursSpent<2" for less that two hours,  "HoursSpent<72" for less that three days, etc.)

David, 

That helped me too much. Thanks for this post.

I can calculate the time between two customfileds value. 

I need one further step. I need to calculate the time between one custom field and issue created time. How can I do this ?

I tried those but did not worked:

1.

<!-- @@Formula:

if (issue.get("customfield_11804")==null || issue.getCreated() == null)

  return null;

diff = (issue.get("customfield_11804").getTime() - issue.getCreated().getTime()) / 1000 / 60;

if (diff < 60)

  return diff + " minutes";

diff = diff / 60;

if (diff < 24)

  return diff + " hours";

return (diff / 24) + " days";

-->

 

2.

<!-- @@Formula:

if (issue.get("customfield_11804")==null || issue.get(“created”).getTime() == null)

  return null;

diff = (issue.get("customfield_11804").getTime() - issue.get(“created”).getTime()) / 1000 / 60;

if (diff < 60)

  return diff + " minutes";

diff = diff / 60;

if (diff < 24)

  return diff + " hours";

return (diff / 24) + " days";

-->

 

Any help will be appreciated. Best Regards,

Levent

Hi Levent,

the second formula should work (although I'm not quite sure about adding a string to a number like you did). Did you check atlassian-jira.log for any error? If there is an error with the formula (such as a syntax error), it will show up in the logs.

Of course, this formula is for a calculated text field, right?

David

Hi David,

Yes it's calculated text field. I have something wrong but can not find out. As my custom filed indexes get some kind of corrupted after creating so many custom fileds at one. I am fixing those errors then I will look at the formula whetget get works or not. 

 

Thanks for your help. I hope to give you good news later on.

Hi David, 

If I return a String in 1w 2d 3h format, which searcher can I use?

Hi Vikas,

if you're returning a String, it means you're creating a Calculated Text Field. In that case, you have a choice between a Free Text searcher (and thus the "~" operator) and an Exact Text Searcher (and the "=" operator). Obviously, you won't be able to search by duration, since it's not a Duration type field (see https://innovalog.atlassian.net/browse/JMCF-228 for that).

David

Hi,
I have similar requirement.  and trying to implement above said solution but  seems to be an issue for me.  Below is the screenshot from my Jira, request you to please assist

Impact_duration_field.jpg

when I'm hovering mouse over red cross it shows below error

Impact_duration_field_1.jpg

 

Hi David & all community,

I have solved my problem. I am using calculted text field. I am not so sure how it's resolved but I guess I had some issues with indexing. I recommend re-index after create or change a custom filed.

 

My sample code is like below:

<!-- @@Formula:
if (issue.get("customfield_11807")==null || issue.get("customfield_11806") == null)
return null;
return (issue.get("customfield_11807").getTime() - issue.get("customfield_11806").getTime()) / 1000 / 3600 / 24; -->

or 

<!-- @@Formula:
if (issue.get("customfield_11808")==null || issue.get("customfield_11807") == null)
return null;
return (issue.get("customfield_11808").getTime() - issue.get("customfield_11807").getTime()) / 1000 / 3600 / 24; -->

 

It works fine. And I cath the time stamps and time duration between status change.

Hope your will work as well.

Best Regards, Levent..

Hi Levent,

I'm not finding calculted text field in jira's custom field. Do I need to install addon for this or need to enable it ? please assist

Regards
Neeraj

Calculated_Text_Field.JPGHi Neeraj, when you go to the "Add Custom Field" there is an advanced section. If you select advanced then you need to see "Calculated Text Field".   I guess it is in Jira's standart setup.. If it is not there probably you will need to install an add-on. In my case I am using Jira standalone server edition 6.2 version..  Hope you will find this option. 

Hi Levent,
thanks for response,   I understand I need to install "Jira Misc Custom Fields "   addon in order to get calculated text field.  I'm also having Jira server on 7.5.2

Regards
Neeraj

Hi Levent,

after editing a calculated custom field, you must indeed re-index, as Jira will kindly suggest by the way (in a message box at the top-right corner of the page). Until you re-index, the field value will only be accurate on View Issue pages, not on tabular views nor for JQL searches.

David

David, thanks for your help. I have another question in a different case. Can you please look at my topic in the link below ? 

https://community.atlassian.com/t5/Jira-questions/How-do-I-get-the-year-in-YYYY-format-on-an-issue-duedate/qaq-p/757971#U757975 

 

Regards, Levent..

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,817 views 18 22
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