Excel "Short Date" format producing incorrect results when brought in from another spreadsheet

Question

Excel "Short Date" format producing incorrect results when brought in from another spreadsheet

Anonymous

Hi,

I've been having this problem for a while now and have found a few workarounds, but it seems like too silly of a problem for me to have to put up with, so I'm hoping someone here will have some insight into what's going on here.

I have two spreadsheets open, one is a sheet with a single identifying column, the other a database. I'm using index / match to put relevant info from the database into the first spreadsheet. Nothing exciting here.

One of the columns I am pulling from the database is a "date" column. In the original database it is formatted as "Short Date". When I bring the data into the first spreadsheet (and paste values), I'd expect that converting my date column to "short date" will yield the same date, but for some reason this isn't the case anymore (for the last month or so.)

I think an example will help illustrate my problem better.

In my database spreadsheet I have the "short date" "9/1/16". When converted to "General" I get the value "42614".

Once the date is brought into my other spreadsheet, I get the value "42614" formatted as "general" as expected. However, when I convert this column to "short date" in this spreadsheet, I get the date "9/2/20".

I read on another forum that occasionally one has to add or subtract 1,462 from a date if the result is incorrect. This ends up being the exact number of days between my expected date "9/1/16" and my calculated date "9/2/20". (https://support.office.com/en-us/article/Change-the-date-system-format-or-two-digit-year-interpretation-e6354061-6c98-4e17-84b4-f122dc6063a7)

This seems silly that I would have to do this each time I run this formula now, particularly because everything worked swimmingly until recently. Any ideas if any settings may have changed with a recent update or something else I can look into?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

Excel can use two date systems, the so-called "1900" and "1904" systems. The former is the default in Excel for Windows, and it originates in Excel's predecessor Lotus 1-2-3. The latter is the default in Excel for Mac. The difference between the two is - as you have found - about 4 years (1904-1900=4).

The date system is a workbook-level setting: it is stored with the workbook, so it is possible to open one workbook that uses the "1900" system and another that uses the "1904" system. If you copy/paste a date between such workbooks, the date will shift 4 years backwards or forwards.

You can easily change the setting for a specific workbook, but that will cause all dates in that workbook to shift by 4 years. To do so:

Select File > Options.
Select Advanced in the navigation pane on the left hand side.
Scroll down to the section 'When calculating this workbook'.
Tick or clear the check box 'Use 1904 date system'.
Click OK.

Answer 2

The explanation is here:Differences between the 1900 and the 1904 date system in Excel

Microsoft changed Mac Excel to use the 1900 date system starting win Office 2011 as a part of their effort to improve compatibility with the Windows platform. My guess is that the workbook you're copying from is based on the 1900 date system but the one you're pasting to is based on the 1904 date system, resulting in a difference of 4 years plus 1 day later.

[Using your example, 9/1/16 is the 42,614th day starting with Jan. 1, 1900, but the 42,614th day from Jan. 1, 1904 is 9/2/20.]

See if it it helps to do either (bit not both) of the following;

In your current version of the program, go to Excel> Preferences - Calculation, then check the box to Use 1904 date system, or
Go to Excel> Preferences - Edit, then check the box to Automatically convert date system

Share via

Excel "Short Date" format producing incorrect results when brought in from another spreadsheet

0 additional answers