How to automatically calculate the overall time by comparing two date/time fields?

[Robert Anthony]

Hi there, Just got a trial of your awesome plugin. I have two fields, the system date created field, and a custom date resolved field. I have a third field (well not yet, not sure of what kind of field to make), but I want it to contain the value (in days, hours preferred) of time total between the date created and the date resolved. Pretty sure your plugin can do this, could you perhaps give me the formula and any other bits of info I need? Thanks, would love to buy the full version if we can do this!

[Fidel Castro Armario]

Hi Robert,

Thank you for your words. You can do it using "Mathematical and date-time expression calculator" post-function with any of the following formulas:

1) Assuming that your Resolved custom field is a Date Picker you should use:

({aaaaa} - {00009}) / {HOUR}

2) Assuming that your Resolved custom field is a Date Time Picker you should use:

(datePart({aaaaa}, LOCAL) - datePart({00009}, LOCAL)) / {HOUR}

replacing aaaaa with field code for your Resolved custom field.

Note that {00009} is field code for "Date and time of creation".

The custom field you should create to store the result of the formula (i.e., the one to be selected in Target field parameter) is a Number custom field.

[Robert Anthony]

Hi Fidel! Woooo, we have values populating!

However if I manually set (for qa) my resolved field on resolution a few days in the future, the number is just a value (216). Is this hours or...? Is there any way I can get the final result field to display it in a value that is easier to digest quickly, like dd:hh format?

[Fidel Castro Armario]

Yes, the number you are obtaining is a number of hours. If you want a number of days, you have to replace {HOUR} term with {DAY} in your formula.

If you have a textual description with days and hours separately you have to:

1) Use a Text custom field as Target field instead of a Number custom field.

2) Use "Copy a parsed text to a field" instead of "Mathematical and date-time expression calculator" using the following configuration:

• Parsing mode: advanced
• Text to be parsed:

floor((datePart({aaaaa}, LOCAL) - datePart({00009}, LOCAL)) / {DAY}) + "days " + (modulus(datePart({aaaaa}, LOCAL) - datePart({00009}, LOCAL), {DAY}) / {HOUR}) + " hours"

replacing aaaaa with field code for your Resolved custom field.

[Robert Anthony]

Hmm, that actually results in a "Bad expression in post-function "Copy a parsed text to a field". Edit post-function configuration and debug entered expression." when I try to resolve.

I replaced BOTH instances of aaaaa with the field code, is that correct?

[Fidel Castro Armario]

Yes, that is correct. I can't give you the field code of custom fields since it depends on each particular JIRA instance. That is why I write custom field codes like {aaaaa}, and ask you to replace it with the corresponding field code in your JIRA instance.

[Robert Anthony]

Hi Fidel, Thank you, turns out I just had an extra " in my formula that was causing problems. Everything is fixed and works, def gonna pick this up!

:)

[Robert Anthony]

this is resolved, but ive created a new problem ill describe in a new ticket.

[Fidel Castro Armario]

Thank you for your decision of using JIRA Workflow Toolbox. I hope you will find it very useful.