Home

Historical dates in Excel (older than 01.01.1900)

%3CLINGO-SUB%20id%3D%22lingo-sub-143213%22%20slang%3D%22en-US%22%3EHistorical%20dates%20in%20Excel%20(older%20than%2001.01.1900)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-143213%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everybody%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20a%20teacher%20in%20History%20of%20technology%20and%20History%20of%20ideas.%20I%20recently%20found%20out%20that%20you%20are%20able%20to%20make%20visual%20timeline%20charts%20showing%20historical%20developments%20by%20using%20the%20Map%20function%20in%20Excel.%20As%20an%20example%2C%20I%20tried%20to%20show%20the%20production%20of%20steel%20in%20England%2C%20France%2C%20and%20Germany%20from%201870-1910.%20The%20purpose%20was%20to%20show%20my%20students%20some%20charts%20about%20the%20industrialization%20in%20Europe.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%2C%20I%20ran%20into%20the%20problem%20that%20Excel%20apparently%20doesn't%20understand%20dates%20before%2001.01.1900%20(I%20read%20that%20this%20is%20the%20day%20with%20the%20value%201).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20there%20any%20workaround%20for%20this%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBest%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEsben%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-143213%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharts%20%26amp%3B%20Visualizing%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-144811%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20dates%20in%20Excel%20(older%20than%2001.01.1900)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-144811%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20try%20using%20the%201904%20date%20system%20(File%20tab%20%26gt%3B%20Options%20%26gt%3B%20Advanced%20%26gt%3B%20When%20Calculating%20This%20Workbook)%2C%20which%20seems%20to%20accommodate%20older%20dates.%20But%20it%20does%20so%20by%20adding%20some%20offset%20to%20the%20date%2C%20and%20it%20may%20mess%20up%20dates%20in%20other%20workbooks%2C%20and%20if%20you%20send%20a%20messed%20up%20workbook%20to%20someone%20else%2C%20it%20may%20mess%20up%20their%20dates.%20Maybe%20it's%20not%20as%20bad%20as%20all%20that%2C%20and%20it%20might%20be%20worth%20trying.%20But%20I%20don't%20really%20know%20how%20it%20works%2C%20and%20I've%20heard%20rumors....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-143449%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20dates%20in%20Excel%20(older%20than%2001.01.1900)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-143449%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20very%20much%20for%20the%20links%20and%20your%20advice%20%3A-).%20It%20was%20very%20useful.%20I%20made%20the%20%22dummy%20column%22%20solution%20with%20the%20wrong%20dates%20(but%20right%20intervals)%20and%20added%20years%20as%20a%20category%20to%20the%20map.%20Then%20I%20hid%20the%20wrong%20dates%20from%20the%20Power%20Map%20presentation.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-143383%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20dates%20in%20Excel%20(older%20than%2001.01.1900)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-143383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Esben%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20fundamental%2C%20lot%20of%20sources%20about%20that%20and%20workarounds%2C%20e.g.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.vertex42.com%2FExcelArticles%2Fcreate-a-timeline.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.vertex42.com%2FExcelArticles%2Fcreate-a-timeline.html%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.exceluser.com%2Fformulas%2Fearlydates.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.exceluser.com%2Fformulas%2Fearlydates.htm%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20for%20the%20PowerMap%20timeline%20the%20workaround%20could%20be%20if%20you%20add%20dummy%20column%20for%20your%20data%20adding%2C%20let%20say%2C%202000%20to%20each%20date%20(e.g.%203895-02-10%20instead%20of%201895-02-10)%20and%20if%20you%20add%20short%20date%20format%20to%20your%20regional%20settings%20with%20two%20digits%20for%20the%20year%20(like%20dd%2Fmm%2Fyy)%20when%20you%20may%20apply%20that%20format%20to%20PowerMap%20timeline.%20It%20will%20show%20your%20time%20without%20centuries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20some%20other%20workarounds%2C%20first%20which%20is%20in%20mind.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-143371%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20dates%20in%20Excel%20(older%20than%2001.01.1900)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-143371%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20your%20reply.%20The%20functionality%20I%20am%20referring%20to%20is%20called%20Power%20Maps%2C%26nbsp%3B%20and%20you%20can%20read%20about%20it%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FGet-and-prep-your-data-for-Power-Map-67e19f9a-22b8-4b89-a604-3ebb49a368e5%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EIt%20is%20a%20pretty%20cool%20feature%20because%20it%20can%20show%20a%20development%20in%20time%20on%20a%20map%20and%20you%20can%20export%20it%20as%20a%20video.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20know%20if%20there%20is%20a%20technical%20reason%20for%20the%20lack%20of%20support%20of%20dates%20before%201%2F1%2F1900%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-143335%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20dates%20in%20Excel%20(older%20than%2001.01.1900)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-143335%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eno%2C%20dates%20before%201%2F1%2F1900%20are%20not%20supported%20in%20Excel.%20You%20could%20try%20using%20just%20the%20year%20number%20instead%20of%20full%20dates%2C%20but%20I'm%20not%20sure%20what%20time%20line%20functionality%20you%20are%20referring%20to%2C%20so%20that%20may%20not%20work.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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
Highlighted

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. 

Highlighted

Hi @Ingeborg Hawighorst

Thanks for your reply. The functionality I am referring to is called Power Maps,  and you can read about it here

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?

 

 

 

Highlighted

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.

Highlighted

Hi @Sergei Baklan 

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. 

 

Highlighted

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....

Related Conversations
Troubleshooting a published Excel formula
mlmat184 in Excel on
4 Replies
Freezing panes
KamJones in Excel on
2 Replies
How can I extract URL from hyperlinks?
tachyglossus in Excel on
2 Replies
fuzzy match on a column
KMorrison5151 in Excel on
0 Replies
copy and past from one sheet to another help
john2tl in Excel on
0 Replies
Hidden External Reference
mtarler in Excel on
3 Replies