Convert calendar date to/from int (Excel®) date using plain old, lightning fast, integer arithmetics.
- What is the int (Excel®) date?
- Application examples
- Alternatives
- Why is this library better
- How to import and use in maven projects
- More details
The int (Excel®) date is the number of days since the beginning of 1900, with a few considerations:
- Day 0 is, somehow, January the 0th, 1900 (✤)
- Day 1 is January the 1st, 1900
- Day 60 is February the 29th, 1900 (✶)
- Day 61 is Thursday, March the 1st, 1900. Since that day everything is normal and correct
✤: 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®:
- Write a number in a cell, and then change the cell format to short date.
- Write a date in a cell, and then change the cell format to number.
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.
- It is easy to use correctly and difficult to use incorrectly
- It is simple and fast because it uses integer arithmetics
- It does ``The Right Thing´´ because it uses integer arithmetics and it does not mess with hours, minutes, seconds etc.
- You can use it with old Java versions
- It is small and has no dependencies (except junit, for testing)
Simply add this dependency to your pom.xml
:
<dependency>
<groupId>com.github.mkrevuelta</groupId>
<artifactId>JIntDatesConverter</artifactId>
<version>1</version>
</dependency>
See:
- The javadoc documentation.
- The project page in GitHub.