Cursed Excel: "1/2"+1=45660

Summary

Quadratic aspires to be the best AI spreadsheet for data analysis, which implies two conflicting goals:Maintain feature parity with Microsoft ExcelBe goodI mean no offense to the authors of Excel; it is fantastic software that we at Quadratic very often hail as the gold standard of spreadsheet interaction. That said, it’s reaching the ripe old age of 40 this year and its semantics seriously suffer from a decades-long accumulation of backwards-compatible cludges.One of my favorite things about working here is that I get to reverse-engineer Excel, find strange quirks in its behavior, and decide what to do about them in Quadratic. I suffer every day so that our users may live blissfully unaware of the undocumented sins committed by Microsoft in the name of compatibility. Today you will gain a glimpse into the horrors I contend with, and then you too will live in fear of Microsoft Excel — not because you lack knowledge, but because you know too much.For many years, geneticists have struggled with Excel’s overeager date parsing applying to names like MARCH1 or SEPT2 that aren’t meant to be dates. But Excel’s date parser has much weirder edge cases.If we type ="1/2" into a cell, then of course it contains the text “1/2”.What if we add 1 to that?45660? What?? Here’s a hint: if you try this in the future, you may get a different number.And it’s not just dates! Sometimes Excel’s time parser bites off a little more than it can chew. Of course typing 10:25 into a cell results in the time 10:25 a.m., but what happens if we type 10:75?0.46875?? Where the heck did that come from?I promise that I will explain what’s going on here, but first we need to cover some technical documentation and some Catholic Church history.In both of these scenarios, we’re tricking Excel into parsing our input as a date or time, but displaying it as a number. As the official documentation for the DATEVALUE() function explains:Microsoft Excel stores dates as sequential serial numbers so they can be used...