JIntDatesConverter

Convert calendar date to/from int (Excel®) date using plain old, lightning fast, integer arithmetics.

Table of Contents

1. What is the int (Excel®) date?
2. Application examples
3. Alternatives
4. Why is this library better
5. How to import and use in maven projects
6. More details

What is the int (Excel®) date?

The int (Excel®) date is the number of days since the beginning of 1900, with a few considerations:

• Day 0 is, somehow, January the 0^th, 1900 (✤)
• Day 1 is January the 1^st, 1900
• Day 60 is February the 29^th, 1900 (✶)
• Day 61 is Thursday, March the 1^st, 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.

Application examples

Problem 1: What's my exact age in days?

Let's say that I was born on February the 20^th, 2000 —not my real birth date, but I do feel that young!—. That was day 36576.

Well, today is June the 28^th, 2020, which is day 44010.

44010 - 36576 = 7434. That would be my age in days!! Easy, isn't it?

Problem 2: How many days do I have to buy a new suit or loose 5Kg?

My friends Alice and Bob are going to get married on May, the 22^nd, 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!

Problem 3: When will Unix time end?

Unix time started on January the 1^st, 1970, which is day 25569.

It will last 2³² = 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 7^th, 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 2³¹ seconds since the beginning of 1970. That leads to... 25569 + 49710/2 = 50424, which is January the 19^th, 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 7^th, 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.

Problem 4: What day of the week will it be on October the 12^th, 3000

October the 12^th, 3000 will be day number 402053. The remainder of 402053 divided by 7 is 1. Hence, it will be Sunday.

Alternatives

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.

Why is this library better

• 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)

How to import and use in maven projects

Simply add this dependency to your pom.xml:

<dependency>
    <groupId>com.github.mkrevuelta</groupId>
    <artifactId>JIntDatesConverter</artifactId>
    <version>1</version>
</dependency>

More details

See:

• The javadoc documentation.
• The project page in GitHub.