SOLVED
Home

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
John Dewey
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
Related Conversations
Average without #VALUE!
Crackeriano in Excel on
0 Replies
multiple same set columns
Jon_E1972 in Excel on
2 Replies
Struggling how to do a calculation
Tim_Jevans in Excel on
4 Replies
vlookup
Novice6 in Excel on
1 Replies
Average set of values if 2 criteria are met
jtorrens in Excel on
1 Replies