Excel, Damn You: "1/2"+1=45660

Translation of an article by Sivchenko_translate, based on original work by Andrew Farkas

Introduction

I'm writing this with absolutely no intention of offending the authors of Excel. It's fantastically successful software that rightfully holds the gold standard among spreadsheet editors.

The author works on reverse-engineering Excel, hunting down quirks in the program's behavior and figuring out how to handle them in their company's own spreadsheet editor.

The Magic of Numbers

Excel's problems with date parsing have been well known for a long time. The program incorrectly interprets names like MARCH1 or SEPT2 as dates.

First example: When you type ="1/2" into a cell, the text "1/2" appears.

But when you add one: ="1/2"+1 gives you 45660.

Second example with time: Entering 10:25 gives you 10:25 AM. However, 10:75 turns into 0.46875.

(Don't) Read the Manual

Official Documentation

"Dates in Microsoft Excel are stored as sequential serial numbers that can be used in calculations. By default, December 31, 1899 is serial number 1."

According to the documentation, January 1, 2008 is serial number 39448.

Errors in the Documentation

There are two inaccuracies:

• Serial number 1 is January 1, 1900 — not December 31, 1899.
• Excel acts as though serial number 0 is "January zeroth, 1900."

Between January 1, 1900 and January 1, 2008 there are only 39,446 days, not 39,448. The offset is 2 days instead of the expected offset of 1.

A History of the Calendar

Calendar Reform

In October 1582, Pope Gregory XIII announced the transition to a new calendar system. The Julian calendar, where every 4th year was a leap year, had drifted so far that Easter no longer coincided with the spring equinox.

The Gregorian Calendar

The new rule: years divisible by 100 are not leap years, except for years divisible by 400. Therefore, 2000 was a leap year, but 1900 was not.

The Lotus 1-2-3 Bug

In 1983, Lotus Software released Lotus 1-2-3 — a revolutionary spreadsheet editor.

The Lotus developers were unfamiliar with the papal decree of 1582 and assumed that 1900 (divisible by 4) was a leap year.

Microsoft copied this bug in the first version of Excel. If you enter Feb 28, 1900 and add one, you get Feb 29, 1900 — a day that never existed.

So Why Does This Happen?

Explaining the First Example

Excel interprets 1/2 as January 2, 2025 (the year the article was written). Adding 1 gives January 3, 2025 — exactly 45,658 days from January 1, 1900.

Accounting for the two reasons described above gives us 45,660, which is exactly what Excel displays.

Explaining the Second Example

Time is represented as a fraction of a day:

• 6:00 = 0.25 of a day (a quarter)
• 10:75 (10 hours 75 minutes) = 11:15 = 0.46875 of a day

By the same logic, 0.46875 should correspond to 11:15 — the moment 75 minutes after 10:00. Excel displays it as a number because it recognizes the input as invalid.

Time Beyond 23:59

You can get time values exceeding one day. Entering 37:30 yields 1.5625 — that's 1:30 PM the next day (serial number 1 is midnight on January 1, 1900; 1.5625 is 1:30 PM on January 1, 1900).

What About Google Sheets?

Google chose a different approach: they simply shifted the first two months of 1900 by one.

So January 1, 1900 corresponds to serial number 2, not 1. This is more logical, but has a drawback: dates before March 1, 1900 are off by one when imported from Excel.