SOLVED
Home

Excel 2013 Date computation bug

%3CLINGO-SUB%20id%3D%22lingo-sub-382578%22%20slang%3D%22en-US%22%3EExcel%202013%20Date%20computation%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382578%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can't%20figure%20out%20what's%20the%20problem%20in%20this%20situation%3A%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20I%20calculate%20the%20sum%20of%20the%20time%20spent%20of%20the%20day%2C%20with%20those%20values%3A%20(see%20attachement)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EStart%3C%2FTD%3E%3CTD%3EEnd%3C%2FTD%3E%3CTD%3EStart%3C%2FTD%3E%3CTD%3EEnd%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3EFormula%3C%2FTD%3E%3CTD%3EBreak%3C%2FTD%3E%3CTD%3EWithout%20break%3C%2FTD%3E%3CTD%3EFormula%3C%2FTD%3E%3CTD%3EEquality%3C%2FTD%3E%3CTD%3EFormula%3C%2FTD%3E%3CTD%3EControl%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E08%3A43%3A00%3C%2FTD%3E%3CTD%3E12%3A11%3A00%3C%2FTD%3E%3CTD%3E12%3A41%3A00%3C%2FTD%3E%3CTD%3E17%3A42%3A00%3C%2FTD%3E%3CTD%3E08%3A29%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3D(B3%20-%20A3)%20%2B%26nbsp%3B%20(D3%20-%20C3)%3C%2FTD%3E%3CTD%3E00%3A30%3A00%3C%2FTD%3E%3CTD%3E07%3A59%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3DE3-G3%3C%2FTD%3E%3CTD%3EVRAI%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3DL3%3DH3%3C%2FTD%3E%3CTD%3E07%3A59%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%3A13%3A00%3C%2FTD%3E%3CTD%3E12%3A42%3A00%3C%2FTD%3E%3CTD%3E13%3A41%3A00%3C%2FTD%3E%3CTD%3E17%3A25%3A00%3C%2FTD%3E%3CTD%3E09%3A13%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3D(B2%20-%20A2)%20%2B%26nbsp%3B%20(D2%20-%20C2)%3C%2FTD%3E%3CTD%3E00%3A30%3A00%3C%2FTD%3E%3CTD%3E08%3A43%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3DE2-G2%3C%2FTD%3E%3CTD%3EFAUX%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3DL2%3DH2%3C%2FTD%3E%3CTD%3E08%3A43%3A00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20first%20line%20is%20correct%20but%20the%20second%20is%20wrong.%3CBR%20%2F%3E%3CBR%20%2F%3EExcel%20say%20that%2008%3A43%20is%20not%20equal%20to%2008%3A43.%20But%20only%20when%20the%20firs%2008%3A43%20is%20calculated%20by%20the%20sum%20of%20the%20day%20minus%20the%2000%3A30%20min%20break.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20fix%20this%20equality%20problem%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20double%20checked%2C%20no%20trim%20problem%2C%20no%20date%20format%20problem.%20I've%20used%20the%20date%20validation%20for%20the%20four%20date%20inputs.%3CBR%20%2F%3EEven%20when%20I%20print%20long%20date%20format%2C%20the%20year%2C%20month%2C%20day%2C%20hour%2C%20minutes%2C%20and%20seconds%20are%20equals.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20add%20one%20minute%2017%3A25%20-%26gt%3B%2017%3A26%20and%20compare%20to%2008%3A44%20it's%20solved%3CBR%20%2F%3EIf%20I%20subtract%20one%20minute%2017%3A25%20-%26gt%3B%2017%3A24%20and%20compare%20to%2008%3A42%20it's%20solved.%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20seems%20to%20be%20a%20rounding%20problem%20but%20I%20haven%E2%80%99t%20found%20millisecond%20in%20excel.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-382578%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388697%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202013%20Date%20computation%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F305990%22%20target%3D%22_blank%22%3E%40Goldridge%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388694%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202013%20Date%20computation%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388694%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20too%20for%20the%20details%20analysis!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388693%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202013%20Date%20computation%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388693%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20it%20solves%20that%20problem!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-382726%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202013%20Date%20computation%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382726%22%20slang%3D%22en-US%22%3E%3CP%3EGoldridge%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20appear%20that%20when%20calculating%20time%20excel%20falls%20over%20sometimes%20in%20rounding%2C%20in%20this%20case%20around%20the%2014th%20decimal%20place%2C%20as%20you%20can%20see%20from%20the%20attached%20example.%20I%20changed%20the%20time%20serial%20number%20to%20a%20number%20format%20so%20you%20can%20the%20decimal%20places%20and%20used%20a%20Round%20function%20for%20both%20the%20time%20calculation%20and%20the%20time%20validation%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20it%20is%20just%20one%20of%20quirks%20of%20excel.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-382707%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202013%20Date%20computation%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F305990%22%20target%3D%22_blank%22%3E%40Goldridge%3C%2FA%3E%26nbsp%3B%2C%20that%20is%20floating%20point%20calculation%20issue.%20In%20Excel%20one%20minute%20is%201%2F24%2F60%2C%20you%20calculate%20them%20as%20floating%20point%20numbers.%20To%20fix%20you%20may%20round%20the%20time%20to%20nearest%20second%3C%2FP%3E%0A%3CPRE%3E%3DMROUND(F2-G2%2C%220%3A00%3A01%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Goldridge
New Contributor

Hi,

I can't figure out what's the problem in this situation:

When I calculate the sum of the time spent of the day, with those values: (see attachement)

 

StartEndStartEndTotalFormulaBreakWithout breakFormulaEqualityFormulaControl
08:43:0012:11:0012:41:0017:42:0008:29:00 =(B3 - A3) +  (D3 - C3)00:30:0007:59:00 =E3-G3VRAI =L3=H307:59:00
07:13:0012:42:0013:41:0017:25:0009:13:00 =(B2 - A2) +  (D2 - C2)00:30:0008:43:00 =E2-G2FAUX =L2=H208:43:00



The first line is correct but the second is wrong.

Excel say that 08:43 is not equal to 08:43. But only when the firs 08:43 is calculated by the sum of the day minus the 00:30 min break.

How can I fix this equality problem?

I have double checked, no trim problem, no date format problem. I've used the date validation for the four date inputs.
Even when I print long date format, the year, month, day, hour, minutes, and seconds are equals.

If I add one minute 17:25 -> 17:26 and compare to 08:44 it's solved
If I subtract one minute 17:25 -> 17:24 and compare to 08:42 it's solved.

It seems to be a rounding problem but I haven’t found millisecond in excel.

Thanks for help

 

5 Replies
Solution

@Goldridge , that is floating point calculation issue. In Excel one minute is 1/24/60, you calculate them as floating point numbers. To fix you may round the time to nearest second

=MROUND(F2-G2,"0:00:01")

 

Goldridge,

 

It would appear that when calculating time excel falls over sometimes in rounding, in this case around the 14th decimal place, as you can see from the attached example. I changed the time serial number to a number format so you can the decimal places and used a Round function for both the time calculation and the time validation value.

 

I think it is just one of quirks of excel. 

Thank you very much it solves that problem!

Thank you too for the details analysis!

 

 

@Goldridge , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies