Forum Discussion
Historical dates in Excel (older than 01.01.1900)
Hi everybody
I am a teacher in History of technology and History of ideas. I recently found out that you are able to make visual timeline charts showing historical developments by using the Map function in Excel. As an example, I tried to show the production of steel in England, France, and Germany from 1870-1910. The purpose was to show my students some charts about the industrialization in Europe.
Unfortunately, I ran into the problem that Excel apparently doesn't understand dates before 01.01.1900 (I read that this is the day with the value 1).
Is there any workaround for this?
Best,
Esben
5 Replies
You could try using the 1904 date system (File tab > Options > Advanced > When Calculating This Workbook), which seems to accommodate older dates. But it does so by adding some offset to the date, and it may mess up dates in other workbooks, and if you send a messed up workbook to someone else, it may mess up their dates. Maybe it's not as bad as all that, and it might be worth trying. But I don't really know how it works, and I've heard rumors....
Hello,
no, dates before 1/1/1900 are not supported in Excel. You could try using just the year number instead of full dates, but I'm not sure what time line functionality you are referring to, so that may not work.
Thanks for your reply. The functionality I am referring to is called Power Maps, and you can read about it https://support.office.com/en-us/article/Get-and-prep-your-data-for-Power-Map-67e19f9a-22b8-4b89-a604-3ebb49a368e5?ui=en-US&rs=en-US&ad=US
It is a pretty cool feature because it can show a development in time on a map and you can export it as a video.
Do you know if there is a technical reason for the lack of support of dates before 1/1/1900?
Hi Esben,
That's fundamental, lot of sources about that and workarounds, e.g.
https://www.vertex42.com/ExcelArticles/create-a-timeline.html
http://www.exceluser.com/formulas/earlydates.htm
As for the PowerMap timeline the workaround could be if you add dummy column for your data adding, let say, 2000 to each date (e.g. 3895-02-10 instead of 1895-02-10) and if you add short date format to your regional settings with two digits for the year (like dd/mm/yy) when you may apply that format to PowerMap timeline. It will show your time without centuries.
Perhaps some other workarounds, first which is in mind.
