Convert calendar date to/from int (Excel®) date using plain old, lightning fast, integer arithmetics.
The int (Excel®) date is the number of days since the beginning of 1900, with a few considerations:
✤: Day 0 is generally used as a default date, meaning ``no date specified´´.
✶: 1900 was not a leap year, but Lotus 1-2-3 considered it a leap year by mistake. Microsoft® Excel® perpetuates that tradition for backward compatibility.
You can play wit this conversion in Microsoft® Excel®:
Let’s say that I was born on February the 20th, 2000 —not my real birth date, but I do feel that young!—. That was day 36576.
Well, today is June the 28th, 2020, which is day 44010.
44010 - 36576 = 7434. That would be my age in days!! Easy, isn’t it?
My friends Alice and Bob are going to get married on May, the 22nd, 2021. That’s day 44338. As I said, today is day 44010…
I have 44338 - 44010 = 328 days. It seems a long time. In theory I could loose those 5Kg. But if we consider Christmas, birthday parties… I’d better make plans to buy a new suit in April!
Unix time started on January the 1st, 1970, which is day 25569.
It will last 232 = 4294967296 seconds. The number of seconds per day is 24 * 60 * 60 = 86400. Therefore, Unix time lasts 4294967296 / 86400 ≈ 49710.27 days.
25569 + 49710 = 75279, which is February the 7th, 2106, early in the morning. Don’t count on me for that apocalypse. I don’t feel that young.
Oh, but this is only valid if you consider the 32 bits unsigned! If it is treated as a signed 32-bits number, the overflow occurs at 231 seconds since the beginning of 1970. That leads to… 25569 + 49710/2 = 50424, which is January the 19th, 2038. Oh my!
Don’t worry about that, though. The world will end even sooner. Network Time Protocol timestamps will experience their 32 bit (unsigned) overflow on February the 7th, 2036. That’s exactly 70 years earlier than the overflow of the unsigned Unix time because NTP timestamps are based on year 1900 instead of 1970 —but I digress.
October the 12th, 3000 will be day number 402053. The remainder of 402053 divided by 7 is 1. Hence, it will be Sunday.
The class java.util.Date stores a date (and time) as the number of milliseconds since the beginning of 1970. It has specific methods to get and set the year month and day, but they are deprecated. Instead, you should use java.util.Calendar. A common practice is to use java.text.SimpleDateFormat to parse strings containing dates. You may need to adjust it to the Lotus 1-2-3 bug. Also, beware of time zones, daylight savings and leap seconds!
Since Java 8 we have the java.time package. It includes the class LocalDate, which stores a date (only a date!). You can construct a LocalDate with LocalDate.of(year,month,day) and then get the epoch day with the method toEpochDay(). For the opposite translation, you can construct a LocalDate with LocalDate.ofEpochDay(dayNumber) and then get the date with getYear(), getMonthValue() and getDayOfMonth(). Yo will need to adjust it to 1900-based day numbers, because LocalDate fixes its zero day in 1970. Also, you may need to adjust it to the Lotus 1-2-3 bug. However, if you enjoy Java 8 or later, this is a reasonably good solution.
Apache POI has a DateUtil class with methods to translate between date and Excel® day number as a double.
Simply add this dependency to your pom.xml:
<dependency>
<groupId>com.github.mkrevuelta</groupId>
<artifactId>JIntDatesConverter</artifactId>
<version>1</version>
</dependency>
See: