The Date Wasn’t Wrong

My Formula Was Just Showing Me the Truth.

There are spreadsheet problems that look complicated until you realize the spreadsheet has been doing exactly what you told it to do.

Photo by Kevin Ku on Unsplash

This was one of those problems.

I was working with an "IFS()" formula that generated text based on different conditions. Part of the output needed to include a date from another cell.

The logic itself was straightforward.

Something along the lines of:

="Add STP " & G2

The expected result was something like:

Add STP 03/15/2023

Instead, the spreadsheet returned:

Add STP 45000

At first glance, it looked like a date-formatting problem.

It was actually a data representation problem.

The Spreadsheet Wasn't Actually Confused

The first instinct when seeing "45000" where a date should be is usually: “Why did my date turn into a number?”

The better question is: “What is the spreadsheet actually storing in that cell?”

Spreadsheets typically store dates as serial numbers.