What do when dates in your Excel file are off by four years

Two incidents happened lately where results I expected to be the same turned out to be different. The first one involved two Excel files sent to me from one of my favorite clients. (You’re all my favorite clients!)

One of the files was an Excel file on a Mac. The other was created on a PC. Then, both files were opened on a Mac and the rows from one file pasted into the other. It all looked lovely when I got it except for the fact that many of the dates were for 2013 and beyond, which didn’t make sense for, say, the date an application was received.

If you use Excel a great deal, you  might already have been aware of the fact that Macintosh and Windows may use different calendars for dates. The default for the Macintosh is to start with January 1, 1904.  The default for windows is January 1, 1900. If you paste data from a Macintosh file into a PC file you can end up with dates that are off by four years and one day.

You can correct this in Excel. Microsoft gives a solution here:

You can correct shifted dates by following these steps:

1. In an empty cell, enter the value 1462.
2. Select the cell. On the Edit menu, click Copy.
3. Select the cells that contain the shifted dates. On the Edit menu, click Paste Special.
4. In the Paste Special dialog box, click Values. Then, select either of the following option buttons.

      Select this     If
      --------------------------------------------------------------------
      Add             The dates must be shifted up by four years and one
                      day.
      Subtract        The dates must be shifted down by four years and one
                      day.

5. Click OK.

Repeat these steps until all of the shifted dates have been corrected.

As Wade Lasson noted on his blog, that takes WAY too many steps if you have 60,000 dates and it is not just as simple as changing the calendar you are using.

In my case, I couldn’t just go to Excel on my Mac, select PREFERENCES , select CALCULATIONS and click off the 1904 Calendar box because then the other half of the records (that came from the PC) would be off by four years and one day in the opposite direction.

Here is one really quick way to fix it using SAS.

First, figure out which of the variables are dates by looking at the output from a PROC CONTENTS.

Second, assuming, as I did, that you have some way of knowing which file had the dates that were off, do this:

DATA  newdates;

SET oldfile;

ARRAY fix{*} list of date variables  ;

IF site = "Macintosh Site" then DO I = 1 TO DIM(fix) ;

fix{i} = fix{i} +1462 ;

END ;

What if you have hundreds of date variables?

I hate to type. If you have hundreds of date variables, do this:

PROC CONTENTS DATA  = datasetname OUT= test  ;
PROC PRINT DATA = test NOOBS ;
VAR name ;
WHERE INDEX(format,"DATE") > 0 ;

This will put the list of date variables in your log. Copy and paste them after the ARRAY statement. That is all.

Oh, don’t whine because you had to spend 5 seconds scrolling down to copy something.