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?
