0

I'm trying to calculate the counts of days between two dates. Here is my code:

public class Main {
    public static void main(String[] args) {
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
        Date date1 = null;
        Date date2 = null;
        String str1 = "01.01.1900";
        String str2 = "16.06.2017";
        try{
            date1 = format.parse(str1);
            date2 = format.parse(str2);
        } catch (Exception e){
            e.printStackTrace();
        }
        long i1 = date2.getTime() - date1.getTime();
        System.out.println(i1/86400000);//milisec to days
    }
}

The result is: 42899 days.

BUT if we recheck that subtraction in EXCEL, the result is 42901 enter image description here

Please, can anybody explain me where is the truth result?

Mateusz Korwel
  • 1,118
  • 1
  • 8
  • 14
Stanly T
  • 964
  • 1
  • 12
  • 30
  • 1
    I cannot more strongly advise against using the legacy `java.util.Date` class. You should instead look in the `java.time` package and find the class most appropriate for your use case (probably `LocalDate` in this case). – Joe C Jun 17 '17 at 09:31
  • You should test your code using closer dates, for example `01.01.1900` and `02.01.1900`. Maybe then you find where is the problem (or maybe Excel count something different than you). – Mateusz Korwel Jun 17 '17 at 09:39
  • the same 2 days difference – Stanly T Jun 17 '17 at 09:43
  • my tests' results : 01.01.2017 - 16.06.2017 = (java: 165) (excel: 166 ) but 01.06.2017 - 16.06.2017 = (java: 15) (excel: 15 ) – Stanly T Jun 17 '17 at 09:50
  • 2
    Your assumption that each day has `86400000` milliseconds is a simplification; this does not take into account daylight savings. – Codor Jun 17 '17 at 09:56

2 Answers2

2

Java round the result down when dividing integer numbers, so I think you have lost a day here:

System.out.println(i1/86400000);//milisec to days

After I tried Java 8 API it shows me 42900 days (note that neither Java 8 nor Excel don't include the last day in the range):

LocalDate from = LocalDate.of(1900, Month.JANUARY, 1);
LocalDate to = LocalDate.of(2017, Month.JUNE, 16);
long daysBetween = ChronoUnit.DAYS.between(from, to);

Another day was calculated by Excel in a wrong way due to a bug as described here. So answering your question - you loose one day on rounding and another day was incorrectly added by Excel.

Danylo Zatorsky
  • 5,856
  • 2
  • 25
  • 49
  • Incorrect. Excel doesn't include the end date either. Problem is in Excel, which incorrectly thinks that 1900 is a leap year. It is not. See: [Is there a bug in Excel concerning dates?](https://stackoverflow.com/q/13722566/5221149) --- You are however correct about the rounding issue, but you don't explain *why* there is a rounding issue, i.e. that it is caused by the loss of an hours when switching to Daylight Savings Time. – Andreas Jun 17 '17 at 11:05
  • @Andreas Thanks for pointing this out - I have updated my answer. – Danylo Zatorsky Jun 17 '17 at 20:11
0

Use Joda Time. It has a better API that what's available in Java's standart library. Here is my code for your case:

package com.example;

import org.joda.time.DateTime;
import org.joda.time.Days;

public class Main{
 public static void main(String[] args) {

    DateTime start = new DateTime(1900, 1, 1, 0, 0, 0, 0);
    DateTime end = new DateTime(2017, 6, 16, 0, 0, 0, 0);

    // calculate  days between two dates 
    int days = Days.daysBetween(start, end).getDays();

    System.out.println("Days = " + days);
 }
}

And the result is Days = 42900

Elvira Parpalac
  • 31
  • 3
  • Why do you call `minusDays(1)`? Is that just to artificially get the same *incorrect* result as Excel? – Andreas Jun 17 '17 at 11:11
  • Oh, I'm sorry for that. I feel I have been a little remiss. Thank you for comment! I'll fix the code) – Elvira Parpalac Jun 17 '17 at 11:38
  • Joda-Time is in maintenance state, the official Joda-Time recommendation is you migrate to `java.time`. Still a better choice than the long outdated `java.util.Date`, though. – Ole V.V. Jun 17 '17 at 17:49