How to implement customized spreadsheet formula fields into java?

Question

I have a requirement where I need to get values based on some excel formula fields. On a high level, we have an excel where user has the liberty to enter date values and based on these input the formula fields are updated and total revenue field value is calculated. Now to perform these validations via code will it be better to

Write own utilities in Java for each formula in spreadsheet.
Use Apache POI to input the values in spreadsheet on runtime and evaluate formulas to get revenue value.

On approach 1 I am having below issues : On implementing below code for =YEARFRAC(B1, B2+1)*12 formula

public static double calculateContractLength(String startdate,String enddate) {
        double monthsBetween = ChronoUnit.MONTHS.between(
                LocalDate.parse(startdate),
                LocalDate.parse(enddate));
        return monthsBetween;
    }

I see mismatch in result of excel formula and above code. For Instance Start Date = 2/12/2020 End Date = 12/31/2022, I see excel showing 34.63 but Code returning 34.0

To better handle is I want to know if there are some existing libraries which have already implemented these spreadsheet formulas or just updating the excel at run time with Apache POI would be a better solution.

https://stackoverflow.com/questions/46985522/java-8-date-time-for-calculating-age-in-decimals — aran, Dec 14 '20 at 03:53
Do you need the exact same result as from Excel? If so, do you know precisely how the `yearfrac` function does? — Ole V.V., Dec 14 '20 at 04:05

Ole V.V. · Answer 1 · 2020-12-14T04:36:02.747

I tend to understand from the documentation of the yearfrac function (link at the bottom) that when given two arguments (as in your example), it will count 30 days in a month. I don’t know whether it’s a requirement that your Java code does exactly the same. In any case here’s my attempt to mimic:

public static double calculateContractLength(String startdate,String enddate) {
    Period per = Period.between(
            LocalDate.parse(startdate),
            LocalDate.parse(enddate));
    return per.toTotalMonths() + per.getDays() / 30.0;
}

Trying it out with the example dates from your question, expecting 34.63:

    System.out.println(calculateContractLength("2020-02-12", "2022-12-31"));

Output is:

34.63333333333333

I also tried the dates from the example in the yearfrac documentation:

    System.out.println(calculateContractLength("2012-01-01", "2012-07-30"));

The documentation says that the result is 0.58055556 years, so that should be 0.58055556 * 12 months = 6.96666672 months. We get:

6.966666666666667

There will probably still be (a bit greater) differences in corner cases. Fractions of months are not well-defined. One sign if the same is that the MS documentation states:

The YEARFRAC function may return an incorrect result when using the US (NASD) 30/360 basis, and the start_date is the last day in February.

Link

Documentation of the YEARFRAC function

How to implement customized spreadsheet formula fields into java?

1 Answers1

Link