Date/Time Arithmetic in Automation Rules

[John Tucci]

Hello, Again, Community:

   I am revisiting my previous automation rules for computing elapsed time-in-current-status and I'm trying to identify the errors in my system date-time arithmetic. I have three automation rules:

• One rule captures current system date whenever an issue transitions to a different status and updates the issue custom field "Issue_Status_Start_Date";
• One rule runs Tue-Sat @ 0100 and gets current system date and updates the issue custom field "Issue_Status_Elapsed_ChkDate";
• One rule  runs Tue-Sat @ 0130 and computes difference between "Issue_Status_Elapsed_ChkDate" and "Issue_Status_Start_Date", in workdays, and updates the issue custom field "Status_Elapsed_WrkDays"

Here is the date arithmetic to compute "Status_Elapsed_WrkDays": 

• Status_Elapsed_WrkDays: 2024-05-03.diff(2024-05-02).wrkdays.abs,

When the automation rule executes, it generates a error in the log:

• Could not convert the field value to a number. Please ensure the value is a number, math expression or smart-value that can be converted into a number.
  • Status_Elapsed_WrkDays: 2024-05-03.diff(2024-05-02).wrkdays.abs,

Does anyone know the proper date arithmetic for this?

Continued "Thanks"!

-John Tucci
  Megabyte Systems, Inc.

[Bill Sheboy · Accepted solution]

Hi @John Tucci 

For a question like this, please post an image of your complete automation rule(s), images of any relevant actions / conditions / branches, an image of the audit log details showing the rule execution, and explain what is not working as expected.  Those will provide context for the community to offer ideas.  Thanks!

Until we see those...

Have you considered merging your second and third rules?

What are the types of your custom fields: text, date, date / time, etc.?  If they are text, please try converting them with toDate prior to performing the diff() operation: https://support.atlassian.com/cloud-automation/docs/examples-of-using-smart-values-with-dates/#Converting-text-to-dates

Kind regards,
Bill

[John Tucci]

Hi, @Bill Sheboy :

My apology for this delayed response...and my sincere "Thanks" for your interest and suggestions, especially the "toDate()" hint, as my date values are text. 

Here are the rules:

• StatusTimer_Initialize_OnTransition
  • Captures the system date when an issue is transitioned and (re)sets the "Elapsed" label (which I use to indicate an issue that has been in its current state for more than 4 workdays):

• StatusTimeLogUpdate
  • Overnight rule to post/update current system date for Status Check Date and compute # workdays in current status

• StatusElapsedFlagger
  • Overnight rule (executes 30 minutes after above rule) to set "Elapsed" label on any issue in current status > 4 workdays

I hope this provides enough info to explain what I'm attempting to accomplish, and to provide insight into what I've missed.

Thanks, again.

-John Tucci

[Bill Sheboy]

Thanks for that information, John.

To summarize, it appears the purpose of your rule set is: detect when an issue has been in the same status for more than 4 working days (i.e., Monday - Friday), in order to add an indictor label.

With text fields to represent the dates, they will need to be converted with toDate before the difference can be done.  To eliminate that, why not store them as date fields rather than text?

Your second and third rules could be combined, eliminating any possible timing problems for rule trigger scheduling.  I'd recommend performing the calculation and saving the result in a created variable, reducing the need for so many re-fetch issue actions.  After the issue updates, the condition from your "StatusElapsedFlagger" rule could be done to set the label.

A couple of follow-up questions:

• What do you expect to happen if an issue transitions rapidly across the board?  Is the working days count irrelevant to track in that situation?
• How is the indicator label removed once it is added?

[John Tucci]

Hi, Again, @Bill Sheboy :

   I'm glad my screenshots made this all clearer. I believe that your pointer to store the date info as date values (instead of text) is the key to making this work! I do also like your suggestion about combining the date update and arithmetic/time-limit detection into a single rule. I will make these changes this week and let you know the results.

For your follow-up questions:

• What do you expect to happen if an issue transitions rapidly across the board?  Is the working days count irrelevant to track in that situation?
  • The working-days count is irrelevant, since the issue won't spend >4 workdays in any given status;
• How is the indicator label removed once it is added?
  • The idea for this automation is to identify issues that "get stuck"; once we review the "stuck" issue, the developer should be able to move that issue to a new status. That transition to a different status triggers the "initialize rule" that sets the "Status_Start_Date" field to the current time, sets "Status_Elapsped_WrkDays" to 0, and clears the "Elapsed" label.

I believe I've mentioned this upcoming opinion in this forum in prior postings, but, I feel that real-time time-in-status data for each issue should be a basic Jira feature. I believe that there is a long-standing request for this already submitted; I even voted for that request!! It just isn't clear to me how many votes are necessary to move a request into the "being developed" stage.

   My sincere "Thanks" to you again for your outstanding help. You are one more element of evidence for the value and character of this Community.

Kind Regards,

-John Tucci

  Megabyte Systems, Inc.|
   Rocklin, CA

[John Tucci]

Uhhh...One More Thing, @Bill Sheboy :

Apology for the serial replies...does simply having the date-tracking fields be a date-value field suffice for having the date-timestamp info stored as a date value, or do I need to specify the date as a formatted string and then use the to_date() function?

Thanks,

-John

[Bill Sheboy]

Using a date or date / time field type for the custom field eliminates the need for conversion with toDate() as that is used to convert text-representations to those types.

That function helps when using text variables for dates (or date / times) and when the value comes from another source, such as typed in by a person or from another system as text.

[John Tucci]

Thanks, Again! Much appreciated.

[John Tucci]

Hello, Again, @Bill Sheboy :

   I verified that the Status_Elapsed_ChkDate and Status_Start_Date fields are Date Fields:

I removed the date-formatting and just used the values from the {{now}} function:

I took your suggestion to unify the two separate rules, and when I ran all of this, the log file contained:

Is there a preferred way to do the date arithmetic rather than the .diff function? It seems that we're getting closer.

Thanks, again, for your diligence and patience.

Regards,

-John Tucci

[Bill Sheboy]

Hi, John!

Would you please post an image of the edit issue action, and the text of the smart value expression, which lead to that error?

As it shows in the audit log, it seems there are literal values in the expression rather than valid smart values, and that does not work for date functions in rules.

Thanks!

[John Tucci]

Hi, Bill:

Here is the Edit Issue action showing what I capture for Status_Elapsed_ChkDate ({{now}}):

And here's the date arithmetic to calculate Status_Elapsed_WrkDays:

Continued "Thanks".

-John

[Bill Sheboy]

Please post the expression you have in that edit for Status_Elapsed_WrkDays, as it is not fully visible in the image.

Also reviewing your log again, I see your diff() call is trying to use a units of measure of "wrkdays".  That is not a valid unit for the function; you could use businessDays instead to count weekdays.

[John Tucci]

{{issue.Status_Elapsed_ChkDate}}.diff({{issue.Status_Start_Date}}).wrkdays

Thanks for "businessDays" tip...will apply that at next edit.

[Bill Sheboy]

Ah, the syntax is incorrect with the placement of curly brackets.  Please try this:

{{issue.Status_Elapsed_ChkDate.diff(issue.Status_Start_Date).businessDays}}

[John Tucci]

Used your suggested syntax and combined the check-date update and elapsed days calculation into a single rule. The rule ran successfully...

...but the elapsed workdays calculation still doesn't seem to work, as that custom field has "None" as the value

Sorry to keep pestering...but your suggestions are leading me in the right direction.

Continued "Thanks",

-John

[Bill Sheboy]

To limit the scope of your testing, I recommend first changing the scheduled trigger to only process a single issue.  (Or add a condition to test for only one issue key.)  Those can be undone after the rule works.

Next, write both of the date values to the audit log before the diff() to confirm they contain what you expect.

Please try that and then post the audit log details to learn what happened.

[John Tucci]

I updated the rule to select a single issue, and I added 2 additional Log Action steps so that each Log Action step writes a single value to the log. Will check the rule results in the morning and update.

Regards,

-John

[Bill Sheboy]

For scheduled trigger rules, you may run them immediately when you need to test them: just look under the ... menu at the top-right to run the rule. 

Of course if that will cause problems due the issue edits, please wait for the schedule.

[John Tucci]

Hi, Again:

FINALLY!!!!!

I cannot thank you enough for your diligence, patience, and dedication in seeing me through this. BTW: Here's the Status_Elapsed_WrkDays equation:

{{issue.Status_Elapsed_ChkDate.diff(issue.Status_Start_Date).businessDays.abs}}

On to nightly automated execution!

Would you mind telling me where you work and your position/title? I will inform the appropriate people on our development and management teams that this is now working, and I want to give you full attribution. This never would have happened without your efforts, knowledge, and generosity.

Sincere Thanks & Deep Gratitude,

-John

[Bill Sheboy]

Awesome; I am glad to learn that helped!

For any attribution, I recommend listing the Atlassian Community as that may help your team when looking for additional solutions in the future.  As for me, I'm between positions at this time.

[John Tucci]

I will mention the Community, as you have suggested. Good luck with your search for your next position. I'm sure that you have plenty of colleagues as references, but I would be pleased, and proud, to be a reference if you so choose.

Please keep me posted. I'm also on LinkedIn, if you want to connect. 

Godspeed.