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!
