Issue with Date-Time comparisons

[excelnovice1010]

Hi

I have a formula which compares two calculated dates and times located in two difference cells (the cells are formatted as Custom dd/mm/yyyy hh:mm:ss). In a few circumstances, even though the dates-times are the same, the formula says they are not. For example, even though 05/06/2013 00:29:48 is in each cell, the comparison says they do not match. When I change the format to the date-time serial number, one is 41430.0206944445 and one is 41430.0206944444 - probably why the formula says they are different.
Does anyone know how I can get around this?
Thanks for your help.

M

[katiespirations]

Are you sure that seconds are the same ?
The problem can be like: 05/06/2013 00:29:47 and 05/06/2013 00:29:48

[arthurbr]

This problem is due to the way MS uses floating point arithmetic. This article describes the problem and possible workarounds

[excelnovice1010]

Hi

Thanks for the help folks. katiespirations - both time and dates are identical. arthurbr - do you know if you can apply rounding to the date-time serial number?

[Tony Valko]

You could try rounding to the nearest second.

A2 = some date/time

=ROUND(A2*86400,0)/86400

[excelnovice1010]

Thanks for your help everyone.

M

[Tony Valko]

You're welcome!