Forum Discussion
Historical dates in Excel (older than 01.01.1900)
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.
Hi SergeiBaklan
Thank you very much for the links and your advice :-). It was very useful. I made the "dummy column" solution with the wrong dates (but right intervals) and added years as a category to the map. Then I hid the wrong dates from the Power Map presentation.
