What to do when the wrong date is pasted in Excel

From Quentin Tarantino's classic, Pulp Fiction:

Vincent: ...But you know what the funniest thing about Europe is?

Jules: What?

Vincent: It's the little differences. I mean, they got the same [things] over there that we got here, but it's just - it's just there it's a little different.

Jules: Example?

Vincent: ...Do you know what they call a Quarter Pounder with Cheese in Paris?

Jules: They don't call it a Quarter Pounder with Cheese?

Vincent: Naw, man, they got the metric system, they wouldn't know what the [heck] a Quarter Pounder is.

Jules: What do they call it?

Vincent: They call it a "Royale with Cheese".

Jules: "Royale with Cheese."

Vincent: That's right.

Although they both use a different versioning nomenclature, the PC and Mac versions of Microsoft Excel are essentially the same app -- except, as in the words of Vincent Vega, they're just "a little bit different." And one of these differences is their respective date systems. By default, Excel for the PC uses the 1900 date system, while its Mac counterpart uses the 1904 date system. In most cases, users won't notice this subtle difference. For example, a file saved by a PC version of Excel using the 1900 date system will be recognized as such when it's read by its Mac counterpart, and vice versa.

But the PC and Mac versions of Excel will recognize and use a different date system only for the given workbook and the sheets within it; new workbooks will use the default date system. And all hell breaks loose when copying and pasting, as well as linking, between two different workbooks with two different date systems. Instead of partying like it's January 1, 1999, your spreadsheet will show that it's actually January 2, 2003.

This is exactly what happens when you copy a date from a 1900-based file and paste it to a 1904-based one.

In the reverse scenario, copying January 1, 1999 from a 1904 based file and pasting it into a cell in a 1900 based file will display December 31, 1994.

One way to correct this behavior is to change your default date system. On the Mac version of Excel, you can do this within the calculation pane of Excel's preferences.

(And because I'm thoughtful, I might as well tell you how to do this on the PC version as well. You can get to it by accessing the calculations tab within PC Excel's options.)

Whether or not you want to change your default date system will obviously depend on your workflow and how often you receive PC-based Excel files, which by and large will come in the 1900 format (this is the default format). In my case, I have the 1900 date system as the default on both my PC and Mac versions of Excel, as most of the Excel files I interact with begin their lives on PCs.

The other fix is to do a simple copy and paste trick. First, enter "1462" (without the quotation marks) into some random empty cell. Copy the "1462" cell, and then right-click and select "paste special" on the cell that's showing the wrong date. Doing a paste special will bring up a dialogue box that gives you a variety of options. The only option you'll need to pay attention to is within the "Operation" pane. If you're pasting to a 1904-based file from a 1900 one, click on "subtract." If you're pasting to a 1900-based file from a 1904 one, click on "add."

Now you're all set, and this is probably more than you'll ever want to know about date systems.

Although I spend considerable time on Apple's own Numbers app (mostly for making my charts look nice), a majority of my spreadsheet partying is done in Excel. And, I must admit, I prefer using the PC version of Excel over its Mac counterpart. It's part familiarity and laziness on my part, as well as functionality. One functional difference, and it's not one of those "little differences" that I alluded to earlier, is that I have a treasure chest of macros -- which all happen to be written in VBA. Unfortunately, Excel 2008 for Mac lacks support for this, although Microsoft plans on reintroducing support for it in the next version of Office for the Mac.