Search code examples
javajava-8java-timefiscal

How to calculate number of months in between 2 dates having custom financial calendar defined?

I've scenario where custom financial calendar is defined. Sometimes the month can be of 28 days or sometimes it can be of 35 days as well. The start of every month varies. (It can start from 10/15/20th)

For referring which calendar date refers to which financial date, I've defined a table which has following column structure: Financial Calendar

In above table you can see that fiscalmonth P1 refers to the date 20190125. (It can be the case that fiscalmonth-P1 is starting from 20190115 to 20190215

This table is in database. I'm working on java part to create functionality which will return Periodbetween object based on two fiscal dates provided(which will refer to yyyymmdd column of above table) to function.

public class PeriodBetween {

    private int days;
    private int weeks;
    private int months;
    private int quarters;
    private int years;
    //getters & setters
}

This periodBetween object gives more information about number of days/weeks/months/quarters in between this two dates. public PeriodBetween between(int startDate, int endDate)

I was looking into something to build like what's there in Java's java.time.Period.between function. But not able to get what best approach I can use by referring to table that is defined above.

Let me know if something like this already exist or there can be other way apart from using Financial Calendar table.

Updates: The scenario of sometimes the calendar cab be of 28 days or 35 days is based on which calendar is followed by customer. So depending on client the Financial Calendar Table will be populated.

Fiscal Calendar is not predictable. It will be pre populated in table based on requirement. Yes the Fiscal Calendar Table follow 52 as well as 52 week support. So to give you higher level of idea the table will be populated based on what type of custom calendar is used by customer.

How I am defining my calendar right now ? Getting the requirement from customer on how they want the calendar to be defined. Using that I'm populating the table mentioned above & using that table everywhere. So let's say on Financial Calendar I want to find out what is 3rd day of P3 month on Financial year 2018. Then my query to this table will be to extract more information on this day will be: select * from financial_calendar where fiscaldayofmonth=3 and fiscalmonth=P3 and fiscalyear=Financial Year 2018

My thoughts of calculating number of months between two dates is to extract information from table using between clause of two dates & divide the data size by 30. Which is incorrect.

Solution

  • AccountingChronology

    The java.time framework in Java 8 and later defaults to using a chronology based on ISO 8601. However, that chronology is pluggable.

    Four other chronology implementations are bundled: Hijrah, Japanese, Minguo, & ThaiBuddhist. Underlying these are the java.time.chrono.Chronology interface, and the java.time.chrono.AbstractChronology class.

    The ThreeTen-Extra library adds more functionality to java.time. This includes providing several more Chronology implementations.

    One of those is AccountingCalendar. This implementation may suit your needs depending on how you define your fiscal calendar (which you neglected to document in your Question). This chronology is designed to follow the 52/53 week fiscal calendar rules as laid down in IRS Publication 538 and the International Financial Reporting Standards.

    Since such a chronology can be defined by each company using the calendar, we need the AccountingChronologyBuilder to define our own company’s definition. Using the builder, we specify:

    • ending day-of-week - The day-of-week on which a given accounting year ends.
    • last-in-month vs. nearest-end-of-month - Whether the ending day-of-week is the last in the month, or the nearest to the end of the month (will sometimes be in the next month.
    • month end - Which Gregorian/ISO end-of-month the year ends in/is nearest to.
    • year division - How many 'months' (periods) to divide the accounting year into, and how many weeks are in each.
    • leap-week month - Which month will have the leap 'week' added to it. In practice this is probably the last one, but this does not seem to be required.

    The builder object then produces a AccountingChronology for us to use in our app.

    From there we can produce AccountingDate objects. This class seems to have much functionality. Notice on this class the method until. To the starting date you provide an ending date, and you get back a java.time.chrono.ChronoPeriod object. From that you might be able to get your count of fiscal months or fiscal weeks. I am not sure, as I have never done any work using this accounting chronology.

    FYI, there seems to be a couple other classes involved in this accounting chronology implementation: AccountingEra & AccountingYearDivision.