• 547K Members
• 2,567 Online
• 652K Conversations
SOLVED

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

Highlighted

# 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.

Highlighted

# Re: Excel 2013 Date computation bug

Thank you very much it solves that problem!

Highlighted

# Re: Excel 2013 Date computation bug

Thank you too for the details analysis!

Highlighted

# Re: Excel 2013 Date computation bug

@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