Jira Automation involving datetime variable

[nick_pearson]

Hi

I have been trying to resolve this problem most of the day and am reaching out for a little help please.

I want to measure the Business Time for an ticket which is In Progress.  I am using a customfield ({{customfield_10678}}) called Date/Time In Progress which is a DateTime Picker field and stores the Date/Time when a ticket moves into the In Progress status.  This is all done by automation and works fine. An example of the contents of the Date/Time In Progress are below:

2025-06-06T06:23:00.0+0000


When the ticket moves out of In Progress another automation compares the Date/Time In Progress with now() to derive the difference in Business Days.  Again this works fine.

Ideally I would want to get the difference between In Date/Time In Progress and now() in businessMinutes() but unfortunately that is not an option with the .diff function.

So the result of the above is that by using .diff with businessDays() I end up with a duration of 4 if the following is used in the .diff formula:

now() = 2025-06-02T09:30:30.7+0000
Date/Time In Progress = 2025-05-30T10:30:00.7+0000

.diff duration using - businessDays() = 2
.diff duration using - days() = 4 

So as said .diff using businessDays() is pretty close to accurate but I now want to calculate the number of business hours the ticket was In Progress on the Friday and Monday between office hours of 09:00 and 17:30. 

This means working the difference between Friday 30th May 09:30 and Friday 30th May 17:30 and then adding those hours to Monday 2nd June 09:00 to 10:30 from the example above.

The problem:

Sorry for the long explanation but I thought some reasonable background may help. 
I made a variable in my automation {{differenceinminutes}} and I am trying to create the date/time in the variable using the:

- YY-mm-dd from the Date/Time In Progress = 2025-05-30
- then appending it with a fixed time of "09:00:00"

But no matter what I try I cannot get the date/time above created in the variable in such a way that I can then apply to the .diff to it to derive the difference in minutes for the Friday and then do it all again for the Monday day.  

Hope you can help

[Bill Sheboy]

Hi @nick_pearson 

Short answer: please try using the date / time withAttribute and toDateTimeAtStartOfDay functions to help:

• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-with-attribute---
• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#To-date-with-time-conversion

Although one may use plusBusinessHours() there is no diff() for that unit of measure yet.

I believe the scenario you describe is possible (as long as you ignore holidays), and it is quite challenging.  It requires thinking of this like a timeline of pieces to add up:

. hour
* business hour
+ day boundary

..........*********....+..........*********....+..........*********....+
            ^                                                 ^
            Start Date Time                                   Now

• capture {{now}} in a variable to use the same value for the remaining steps, perhaps named varNow
• for your Start Date Time, find the remaining business minutes in that day, saving in a variable
• for {{varNow.toDate}} find the business minutes elapsed since the start of the day, saving that in a variable
• use diff() for the middle part to find the business days between Start Date Time shifted to the start of the next day and varNow shifted to the start of that current day, and multiply that by your working business-minutes per day
• add the pieces up to get the total business minutes
• add edge case handling: same day, days reversed, one-off errors, etc.

Given the challenges it may be easier to investigate a marketplace app to help with this type of "working time" tracking.

Kind regards,
Bill

[nick_pearson]

Hi Bill

Thank you for the advice above it worked a treat.  It took me some time to manage all the edge cases around differing Start and End Dates but I have now managed to write the Automation to capture Business Minutes regardless of the Start and End dates and the impact of weekends.  

I've not yet managed to cover the impact of national holidays but will give it some thought to try and design a method that does not require manual maintenance of national holiday dates.

Thanks

Nick

[Bill Sheboy]

Well done!

For holidays, I experimented with that a few years ago when Lookup Tables were added, and never found a good enough solution with simple rule upkeep / maintenance.

Another workaround I recall from other posts:

Write an external service in another programming language which makes the scenario easier to solve (including holidays) and then call that service using the Send Web Request action.