UPDATE: See the accepted answer at the bottom of the accepted thread. Hello All, I am trying to calculate fractional business days between two date/time fields. I have gotten it to work for calendar days (see below), but I cannot make this work between two date/time fields for business days. {{#=}}ROUND({{issue.customfield_11111.diff(issue.Resolved).millis}} / (1000*60*60*24), 3){{/}} Example: * Date 1: Jan 12, 2023 8:20am * Date 2: Jan 17, 2023 4:40pm * Answer for calendar days: 5.347 Days * Answer for business days: 3.347 Days (desired answer) I tried to put businessDays in place of millis and removed the calculation at the end but I cannot get the value to be in fractional days. Can anyone help me with solving this problem? Thanks, Austin

Hi @Austin Churchill There is an old suggestion to support this in rules but I could not find a more recent one in the public Jira backlog: https://codebarrel.atlassian.net/browse/AUT-62 I believe the business days calculations are always integers and for Monday-Friday, 9 am to 6 pm. These behaviors are often limitations when your working days are different, or when you want to remove holidays. There are a couple of work-arounds, depending on how accurate of an answer you need: * when need very accurate results* work with your team/company to build a REST API service to accurately compute the value, and then call that service from the rule using a web request. I recall a couple of people in the community said they did this because their work days were different from the built-in ones. * when less accurate results are acceptable* compute the value using business days diff, and... * use other math operations on the start and end date/time values to make adjustments * test this quite a bit for different cases * bonus: using conditions remove any extra days spanning holidays for your company Kind regards, Bill

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Calculate Fractional Business Days Between Two Date/Time Fields

Austin Churchill_mvwty7

UPDATE: See the accepted answer at the bottom of the accepted thread.

Hello All,

I am trying to calculate fractional business days between two date/time fields. I have gotten it to work for calendar days (see below), but I cannot make this work between two date/time fields for business days.

{{#=}}ROUND({{issue.customfield_11111.diff(issue.Resolved).millis}} / (1000*60*60*24), 3){{/}}

Example:

Date 1: Jan 12, 2023 8:20am
Date 2: Jan 17, 2023 4:40pm
Answer for calendar days: 5.347 Days
Answer for business days: 3.347 Days (desired answer)

I tried to put businessDays in place of millis and removed the calculation at the end but I cannot get the value to be in fractional days. Can anyone help me with solving this problem?

Thanks,

Austin

Accepted answers

Bill Sheboy

Hi @Austin Churchill

There is an old suggestion to support this in rules but I could not find a more recent one in the public Jira backlog: https://codebarrel.atlassian.net/browse/AUT-62

I believe the business days calculations are always integers and for Monday-Friday, 9 am to 6 pm. These behaviors are often limitations when your working days are different, or when you want to remove holidays.

There are a couple of work-arounds, depending on how accurate of an answer you need:

when need very accurate results
- work with your team/company to build a REST API service to accurately compute the value, and then call that service from the rule using a web request. I recall a couple of people in the community said they did this because their work days were different from the built-in ones.
when less accurate results are acceptable
- compute the value using business days diff, and...
- use other math operations on the start and end date/time values to make adjustments
- test this quite a bit for different cases
- bonus: using conditions remove any extra days spanning holidays for your company

Kind regards,
Bill

All comments

Austin Churchill_mvwty7

@Bill Sheboy I have seen you on many of these threads, hoping you can assist here. thanks in advance!

Bill Sheboy

Hi @Austin Churchill

There is an old suggestion to support this in rules but I could not find a more recent one in the public Jira backlog: https://codebarrel.atlassian.net/browse/AUT-62

There are a couple of work-arounds, depending on how accurate of an answer you need:

when need very accurate results
- work with your team/company to build a REST API service to accurately compute the value, and then call that service from the rule using a web request. I recall a couple of people in the community said they did this because their work days were different from the built-in ones.
when less accurate results are acceptable
- compute the value using business days diff, and...
- use other math operations on the start and end date/time values to make adjustments
- test this quite a bit for different cases
- bonus: using conditions remove any extra days spanning holidays for your company

Kind regards,
Bill

Austin Churchill_mvwty7

Hey @Bill Sheboy, I somehow didn't see your response here. Thanks so much for the prompt response. I won't have the resourcing to complete the first option (very accurate results) but I would like to implement the less accurate option. Can you help me with the syntax with the business days diff in my scenario in the initial question? How does my example in the prompt change with business days diff?

Bill Sheboy

Curious timing you just asked about this again...I just did this calculation in a spreadsheet to build a support ticket on the built-in, Atlassian interpretation of a control chart, possibly having an error.

My basic calculation idea was:

identify your start and ending time of day for business hours
use diff().businessDays to get the initial value, subtract 2 from that (for the start and end days), and clamp it at 0 to prevent negatives...saving the result
get the fractional day value for the starting day using diff hours and date/time adjustments
get the fractional day value for the ending day
add the three numbers

I have not tried implementing this in a rule yet. If you get stuck, please let me know and I will create a test rule for it.

Austin Churchill_mvwty7

hey, @Bill Sheboy I was able to follow you on the business days diff and subtract the start and end days. So for example I did a test date range of 14 days, it had 4 weekend days included and then I subtracted 2 to get 8 days. From there I am stuck trying to calculate the fractional days on the start date and the end date. You can see what I have below (it does not include the fractional days. I assume I add (+) two forms of diffs after the -(2) below? But what is the smart value I use? Am I using the date diff format repeated twice? For instance, my thought is it would involve a diff of the following but I don't know the syntax for grabbing the start of a day or end of a day off of a date field value. Can you assist with that?

Start date: diff including customfield_11111 and something to show the end of the day of that custom field.
End Date: diff including resolved and something to show the start of the day of the value in the resolved date

{{#=}}ROUND({{issue.customfield_11111.diff(issue.Resolved).BusinessDays.abs}} - (2)}}, 3){{/}}

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

cloud

jira-software

jira-software-cloud