Calculating Number of Days within a Service Contract that are within a Fiscal Year

[shelleyc]

Scenario:

Our program has service contracts that don't necessarily follow our fiscal year (July 1 - June 30). The service contracts can be anything ranging from Jan 1 - Dec 31, 9/15 - 9/14 etc. They are all a year in length. We have a service fee (Biotics Fee) that we charge annually. This varies by program. What I need to know is how many days in the service period fall within the Fiscal Year so that I can calculate how much of that fee is captured in this Fiscal Year (and ultimately in either the previous or next FY as well). Once I have the days in Fiscal Year, I can pretty easily figure out the rest. I can either trigger this by an automation to run once a day or have some sort of calculated field (but I couldn't find that available to use)

I have the following fields:

Service From: (DATE) Sept 01, 2024

Service To: (DATE) Aug 31, 2025

Fiscal Year Start Date: July 01, 2024

Fiscal Year End Date: June 30, 2025

Biotics Fee: 18,000

Days in Fiscal Year: ???

Fee in FY: ???

I know from an Excel formula that the days in FY should be 303 and the Fee in FY should be 14942.47. I took the Excel Formula which was:

=MAX(0,MIN($B2,DATE(2025,6,30))-MAX($A2,DATE(2024,7,1))+1)

where B2 is Service To and A2 is Service From and tried to put that into jQL like this:

Min({{issue.Service To}},{{Issue.Fiscal Year End Date}})) - MAX({{Issue.Service From}}, {{Issue.Fiscal Year Start Date}}+1)

Which actually does run, but the result isn't correct (it shows -31).

Any help would be appreciated!

[Bill Sheboy]

Hi @shelleyc 

Comparing what you show, your smart value expression does not match the spreadsheet formula: the first part is missing the outer MAX(0, ...) before performing the subtraction.

I also recommend writing each part to the audit log in your rule and comparing that to the spreadsheet results.  That may clarify where there are other differences.

Kind regards,
Bill