Calculate Fractional Business Days Between Two Date/Time Fields

[Austin Churchill]

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

[Bill Sheboy · Accepted solution]

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

[Austin Churchill]

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]

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){{/}}

[Bill Sheboy]

First, three things to consider for implementation of this approach:

1. Identify the precision you want.  I recommend going no lower than hours, as that will give you about 0.05 days resolution.
2. identify your starting and ending time per day (e.g., 8am to 5pm).  If you do not care, assume 0000 to 2359 every day.
3. identify how robust of a solution you want, based on actual usage.

The first and second ones help with implementation, and the third with complexity and the number of test cases. Such as:

• Can work start and end on the same day?  If so, this is a special case.
• Can work start on a non-business day (e.g., weekend day)?  If so, this is a special case.
• Can work end on a non-business day? If so, special case.

I highly recommend drawing some timeline, line charts to plot your test cases before starting.

Now onto some how-to stuff:

After you have handled special cases with if/else conditions, I recommend using three created variables for the three parts to add: starting day, middle days, and finishing days.  We already covered middle days (date diff of business days, minus 2)

Let's assume you want to count whole days as business days for the clock.  The finishing day example would be the difference between the beginning of day and finishing work, or Resolved...which just the hours in the day.  This will be in UTC so please adjust to your time zone, as needed.  

{{#=}}{{issue.Resolved.format("H")}} / 24{{/}}

If instead you wanted to start at a specific time of day, you would substract that value and divide by your length of day.  Let's assume a start of day at 8am, and a 9h day, including 1h for lunch.  Again, please adjust for time zones.

{{#=}}{{issue.Resolved.toStartOfDay.diff(issue.Resolved).hours.minus(8)}} / 9 {{/}}

The starting day calculation would be similar, except measuring from the beginOfWork datetime value to the end of the day.

[Austin Churchill]

Hey @Bill Sheboy, thanks for the detailed answer. Let me try to add some answers to your questions.

I do not want to start or end at a specific time during the day. I essentially want to cover Monday - Friday 24 hours a day as we have people all over the world (Eastern Europe, South America, North America, etc.). So essentially I only want to have the "clock stop" Saturday at midnight and have the "clock start" back up Monday at midnight. I hope that simplifies this.

So if this is the case it looks like the below would be the format for the ending day:

{{#=}}{{issue.Resolved.format("H")}} / 24{{/}}

Starting day wouldn't need to be explicitly stated since it is already part of the middle days.

Middle days would be: 

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

 And then all together would be:

{{#=}}ROUND(({{issue.customfield_11111.diff(issue.Resolved).hours.abs}} / 24) - (2) + {{#=}}{{issue.Resolved.format("H")}} / 24{{/}}, 3){{/}}

After doing all of this there are two problems that I am not sure how to resolve.

1. the "-(2)" that subtracts a weekend only works if the time between customfield_11111 (which is the time an issue moves out of in progress and into code review) and resolved includes one weekend. But if the issue spans 2 weekends (not ideal but it happens), then it won't subtract the correct amount of days.
2. For my test, I had the time span 3 weekends (6 days subtracted) and I put "- (6)" into the equation just for testing until I can figure out #1 above. The number that came out had the correct number of days but the wrong 3-point decimal.
   • customfield_11111 = March 06, 2023 2:56PM PST
   • resolution date = March 29, 2023 3:28PM PST
   • this should be 17.022 days (down from 23 since 6 weekend days) but i get 17.875

[Bill Sheboy]

The -2 days I noted was to subtract the first and second days in the entire range, and was unrelated to weekends.  The use of businessDays in the diff takes care of the weekends.

[Austin Churchill]

@Bill Sheboy ahh I see now. Ok so I have iterated on the solution and below is what I have in place (ill create variables at the end to clean it up). I am getting answers that a correct (within about 10 minutes).

{{#=}}ROUND({{issue.customfield_11111.toStartOfDay.diff(issue.Resolved.toStartOfDay).businessDays.abs}} - 1 + {{issue.Resolved.toStartOfDay.diff(issue.Resolved).hours}} / 24 + {{issue.customfield_11111.toStartOfDay.diff(issue.customfield_11111).hours.minus(24).abs}} / 24, 3){{/}}

Details:

• 1st Calc: this is the number of business days not including the first and last day. I do this in the first diff by moving the custom field and resolved to the start of the day. Then I subtracted 1 day for the first day since we don't want to include the entire first day. This leaves me with only the full business days.
• 2nd Calc: this one handles the last day (the day the issue was resolved). I am doing a diff from the start of the resolved day to the resolved time.
• 3rd Calc: this one handles the first day since we subtracted that out in the 1ast calc. I am doing the same as the last day but subtracting 24 hours to get the inverse (time from that date custom field to the end of the day.
• customfield_11111 = March 13, 2023, 11:00 AM PST
• resolution date = April 4, 2023, 9:09 AM PST
• this gives me 14.917 business days (down from 20 since 6 weekend days). When I calculate this out I get 14.923, which is 0.006 off (8.52 minutes off). I find this an acceptable deviation from the correct value.

[Austin Churchill]

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