SOLVED

wrong date calculate

%3CLINGO-SUB%20id%3D%22lingo-sub-2528144%22%20slang%3D%22en-US%22%3Ewrong%20date%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2528144%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EI%20am%20using%20Excel%20to%20create%20a%20database%20and%20I%20got%20an%20error%20in%20the%20Excel%20calculation.%3CBR%20%2F%3EI%20am%20attaching%20a%20file%2C%20why%20in%20E117%20the%20value%20is%20too%20strange%3F..%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2528144%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2528371%22%20slang%3D%22en-US%22%3ERe%3A%20wrong%20date%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2528371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098595%22%20target%3D%22_blank%22%3E%40elegatec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20a%20rounding%20error%20caused%20by%20the%20way%20dates%20and%20times%20are%20represented%20in%20binary%20notation.%20To%20get%20rid%20of%20the%20discrepancy%2C%20use%20the%20ROUND%20function.%20In%20E3%3A%3C%2FP%3E%0A%3CP%3E%3DROUND((C3-D2)*1440%2C0)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2528391%22%20slang%3D%22en-US%22%3ERe%3A%20wrong%20date%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2528391%22%20slang%3D%22en-US%22%3EA%20well%20know%20%22issue%22%20of%20Excel%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%3C%2FA%3E%3C%2FLINGO-BODY%3E
New Contributor
Hello,
I am using Excel to create a database and I got an error in the Excel calculation.
I am attaching a file, why in E117 the value is too strange?..
3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@elegatec 

It is a rounding error caused by the way dates and times are represented in binary notation. To get rid of the discrepancy, use the ROUND function. In E3:

=ROUND((C3-D2)*1440,0)

Fill down.

@Hans Vogelaar 
Thank you, now it look ok