Why can’t I use dates before 1/1/1900? I am getting 2 different results for the same calculation, how is that even possible? You might be asking yourself these types of questions, but worry not, Excel can be quirky at times. It may not be your fault after all.

No software is without its oddities. In this article, we’re going to take a look at some of the well-known issues, bugs, and workarounds in the recent versions of Excel. You can download a compressed package of all the examples we’re going to be using in this blog by pressing the button below.

15-digit limit

15-digit limit of Excel can cause unexpected errors out of the blue, and give inconsistent results when working with very large or very small numbers. Although Excel can display more than 15 digits, displayed values are not the actual values that are used in calculations.

For instance, looking at the example below you would think that the cells C3 and C5 are equal. However, adding 1 to C3 changes the precision level substantially. There are 11 1's in C4, while there 15 in C3. Ultimately, we get a completely different result in C5, which supposedly contains the reverse function.

The reason for this is that Excel uses a modified version of IEEE 754-1985 specifications. The IEEE 754 is a technical standard for floating-point computation established in 1985 by the Institute of Electrical and Electronics Engineers (IEEE).

Unfortunately there are no workarounds to prevent this from happening.

Dates and leap years

Year Zero

Excel can’t work dates before 1/1/1900, because it evaluates dates as numbers, starting from 1/1/1900. Briefly, 1 stands for 1/1/1900 and 43465 stands for 12/31/2018. However, this means that Excel doesn't recognize any date before 1/1/1900 and you can't use any date/time functions for anything before.

Notice the dates before 1/1/1900 are the ones left-aligned and date functions like YEAR(), MONTH() and DAY() return #VALUE! errors when using these cells.

Day Zero

In Excel, day 1 of the universe is 1/1/1900. You might think the day 0 would be 12/31/1899, but the answer is no. In such cases, Excel makes up a data (1/0/1900) to represent the day value of 0.

Leap Years

Excel treats February 29, 1900 as a valid date, meaning that 1900 is a leap year for Excel. This bug is a heritage from Lotus 1-2-3 to save computer memory. Excel carries this legacy for a similar reasons, for compatibility and preventing errors.

The example below shows that 2/29/1900 works fine with date functions. Fortunately, this bug doesn't occur for any other years.

0⁰

Not really a bug, but zero to the power of zero is a controversial subject even among mathematicians. While some define 0⁰ as 1, whereas others say it’s undefined. As you can imagine, different software have the same conflict. Here are some examples,

This is how it works in Excel,

VLOOKUP & HLOOKUP

This is not a straightforward calculation bug, but a refresh issue. Let's assume that we have a 3-column table and we use VLOOKUP to grab some values. We have 4 cells containing VLOOKUP functions and they get a value from the 3^rd column. Now, if we add a new column between the 2^nd and 3^rd; our target column will shift to the 4^th column, and the VLOOKUP functions should return 0 because the column is empty.

The example below shows that cell H3 is 0 while cells H4 and H5 contain values. However, we can see that they still point to 3^rd column in range B3:E11.

However, this is not really a bug. Simply press CTRL + SHIFT + ALT + F9 keys to force a full recalculation. HLOOKUP shows this same behavior too. It seems this is a bug that occurs on the 2013 and 2016 versions of Excel.