SOLVED
Home

Using dates preceding 1900 in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-809978%22%20slang%3D%22en-US%22%3EUsing%20dates%20preceding%201900%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809978%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20incorporate%20the%20below%20into%20my%20existing%20spreadsheet%20in%20order%20to%20correctly%20format%20dates%20preceding%20the%20year%201900.%20However%2C%20I%20am%20an%20excel%20novice%20and%20cannot%20seem%20to%20figure%20it%20out%3F%20I%20have%20attached%20my%20original%20spreadsheet%20titled%20Family%20Newspaper%20Articles%20and%20the%20Earlydates%20spreadsheet%20workaround.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-809978%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810097%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20dates%20preceding%201900%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810097%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20that%20the%20cycle%20repeats%20every%20100%20years%20but%20trying%20the%20pattern%20didn't%20seem%20to%20be%20quite%20consistent.%26nbsp%3B%20What%20appears%20to%20work%20is%20that%20adding%202000%20years%20is%20the%20same%20as%20adding%20730485%20days.%3C%2FP%3E%3CP%3ESo%20to%20get%20a%20datevalue%20(negative)%20for%20a%20date%20dd%2Fmm%2Fyyyy%20in%20the%201700s%20(say)%20add%202000%20to%20the%20year%20and%20use%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20DATE(%202000%2Byyyy%2C%20mm%2C%20dd%20)%20-%20730485%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoing%20the%20other%20way%20adding%20730485%20to%20a%20negative%20serial%20date%20will%20show%20the%20correct%20day%20and%20month%20but%202000%20years%20forward.%3C%2FP%3E%3C%2FLINGO-BODY%3E
J-Griff
Occasional Contributor

Hello,

 

I am trying to incorporate the below into my existing spreadsheet in order to correctly format dates preceding the year 1900. However, I am an excel novice and cannot seem to figure it out? I have attached my original spreadsheet titled Family Newspaper Articles and the Earlydates spreadsheet workaround. 

 

Any help is greatly appreciated. 

 

Sincerely,

 

John 

 

1 Reply
Highlighted
Solution

@J-Griff 

I thought that the cycle repeats every 100 years but trying the pattern didn't seem to be quite consistent.  What appears to work is that adding 2000 years is the same as adding 730485 days.

So to get a datevalue (negative) for a date dd/mm/yyyy in the 1700s (say) add 2000 to the year and use 

= DATE( 2000+yyyy, mm, dd ) - 730485

 

Going the other way adding 730485 to a negative serial date will show the correct day and month but 2000 years forward.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies