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
Highlighted
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
Highlighted
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")

 

Highlighted

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. 

Highlighted

Thank you very much it solves that problem!

Highlighted

Thank you too for the details analysis!

 

 

Highlighted

@Goldridge , you are welcome

Related Conversations
Creating Pie charts.......
dougler2020 in Excel on
0 Replies
How to use the trendline to find percentage change?
fishfish31 in Excel on
0 Replies
tableau
Noemie911 in Excel on
4 Replies
Summarzing data
Ravi_Kumar in Excel on
1 Replies