Search code examples
javaexceldurationlocaldate

Java Duration giving different result to Excel

have a method that calculates the difference between 2 LocalDateTimes. I have worked out some test data in Excel, but the Java method isn't tallying up with my Excel formula. I'm guessing ym Excel formula is wrong but can anyone tell me why?

    private Duration difference(LocalDateTime request, LocalDateTime response) {
    Duration duration = Duration.between(request, response);
    System.out.println("difference: " + duration.toMillis());
    return duration;
}

My input data:
Request: 2018-02-12T13:43:46.456 Response: 2018-02-12T14:43:54.123

Excel formula result: 3608333 millis Java method: 3607667 millis

My excel formula is the accepted answer here

Solution

  • The operator (OP) is 'confused', Excel is not. Taking the same approach as the Accepted answer referred to:

    Millisecond diffeence

    The formulae displayed in Row1 and Row6 are as used for Rows3,4,5, and 7 after copying down.

    Less cumbersome (but the same result of 3607667) from the time element of the examples:

    =(TIMEVALUE(A5)-TIMEVALUE(A4))*86400000

    or if to be applied to the entire DateTTime strings in the same cells as above:

    =(TIMEVALUE(RIGHT(A5,12))-TIMEVALUE(RIGHT(A4,12)))*86400000

    The Excel result in all these cases:

    3607667