• 408K Members
• 8,881 Online
• 464K Conversations
SOLVED

New Contributor

# Excel 2013 Date computation bug

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)

 Start End Start End Total Formula Break Without break Formula Equality Formula Control 08:43:00 12:11:00 12:41:00 17:42:00 08:29:00 =(B3 - A3) +  (D3 - C3) 00:30:00 07:59:00 =E3-G3 VRAI =L3=H3 07:59:00 07:13:00 12:42:00 13:41:00 17:25:00 09:13:00 =(B2 - A2) +  (D2 - C2) 00:30:00 08:43:00 =E2-G2 FAUX =L2=H2 08: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

# Re: Excel 2013 Date computation bug

@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")`

# Re: Excel 2013 Date computation bug

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.

# Re: Excel 2013 Date computation bug

Thank you very much it solves that problem!

# Re: Excel 2013 Date computation bug

Thank you too for the details analysis!

# Re: Excel 2013 Date computation bug

@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