Forum Discussion
Solved
Error when subtracting dates in excel
I have an excel file with a register of the entry and exit times of a site and there is a cell in which I get #### and if I display it I get a very small negative number. It doesn't cause me any problem but it could be a bug that just with this data a strange thing happens.
To make sure that the format is the same I copied the format of the other cells that work.
As you can see, if I separate the operations and take them out of the IF function, the result is 0 and not ####.
I can only repeat myself: Negative time value are displayed with ########.
If I put the "same" data in my spreadsheet it looks a little different.
The reason behind the tiny decimal number is the "floating point error".
You could round your result. Or you could convert the time values into decimal minutes (x60x24) and convert the result back into a time value (/60/24). Change the number format accordingly.
=D5*24*60-C5*24*60-($F$2*24*60+$F$3*24*60)
- Negative time values are always displayed with ######.
But the value itself will not change.Detlef_Lewin
It is 0 the value of that cell. That cell has an if operation where it checks if it is Friday or not to do one operation or another. If I do the operations without that IF, what appears on the right, which is 0:00, appears, but if I put it in the if, #### appears. The numbers are as follows
16:28:00 - 7:35:00 - 8:23:00 + 0:30:00Doing the next minute and the previous minute doesn't happen and it doesn't happen to me with any other value that matches 0
scientific valuenumeric value
Why is this happening? Is it a very low level bug? It does not generate any problem for me because the truth is that this excel is to have my own control but now I am simply curious about why this happens
I can only repeat myself: Negative time value are displayed with ########.
If I put the "same" data in my spreadsheet it looks a little different.
The reason behind the tiny decimal number is the "floating point error".
You could round your result. Or you could convert the time values into decimal minutes (x60x24) and convert the result back into a time value (/60/24). Change the number format accordingly.
=D5*24*60-C5*24*60-($F$2*24*60+$F$3*24*60)
