SOLVED

Date calculation bug in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-198522%22%20slang%3D%22en-US%22%3EDate%20calculation%20bug%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198522%22%20slang%3D%22en-US%22%3E%3CP%3E1900%20was%20not%20a%20leap%20year%20due%20to%20a%20special%20century%20rule%20in%20the%20Gregorian%20calendar%2C%20but%20Excel%20is%20not%20handling%20this%20rule%20correctly%2C%20causing%20issues%20when%20calculating%20dates%20as%20well%20as%20when%20converting%20spreadsheets%20using%20Microsoft.Office.Interop.Excel.%20Dates%20prior%20to%203%2F1%2F1900%20are%20off%20by%20one%20day.%20Meanwhile%20MS%20SQL%20Server%20does%20handle%20these%20date%20rules%26nbsp%3Bcorrectly%2C%20as%20illustrated%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20337px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F35027i0A75ACDB45277E47%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20Date%20Calculation%20Issue.JPG%22%20title%3D%22Excel%20Date%20Calculation%20Issue.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-198522%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198534%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20bug%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198534%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20known%20issue%2C%20please%20see%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F214326%2Fexcel-incorrectly-assumes-that-the-year-1900-is-a-leap-year%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F214326%2Fexcel-incorrectly-assumes-that-the-year-1900-is-a-leap-year%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198523%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20bug%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198523%22%20slang%3D%22en-US%22%3EI%20am%20running%20Excel%202013%20(15.1.5031.1000)%2032%20bit%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

1900 was not a leap year due to a special century rule in the Gregorian calendar, but Excel is not handling this rule correctly, causing issues when calculating dates as well as when converting spreadsheets using Microsoft.Office.Interop.Excel. Dates prior to 3/1/1900 are off by one day. Meanwhile MS SQL Server does handle these date rules correctly, as illustrated below:

 

Excel Date Calculation Issue.JPG

2 Replies
Highlighted
I am running Excel 2013 (15.1.5031.1000) 32 bit
Highlighted
Best Response confirmed by John Dewey (New Contributor)