Time displayed in weird way

[Crackerdaq]

Hi,

I have attached a sample sheet as easier to see.

Basically the result in n3 displays as 0:00 and the result in n4 as -0:00. Same for o3 and o4.

I have been over and over and over it but can't find any reason at all it should do this?

I want 0:00 to display as 0:00 and anything minus as -0:00

Have set up as [h]:mm;[Red]-[h]:mm and also tried with a 0 on etc. Just not having it.

Any help appreciated. Cheers!

[judgeh59]

I'm not sure if you are asking why there is a -0:00 in cell N4 or why the -0:00 in cell N4 is not Red...

I used evaluate formula ability is XL to "see" what the formula was doing....in N4 the M4-B4 results is -3.0531331779198E-16 (wow, lot of resolution) AND a negative number
in cell N3 the results for M3-B3 is 5.55111512312578E-17 (more resolution) AND a positive number....and because you are using only 0:00 you never see that resolution....

is this your question?

[Pepe Le Mokko]

You are the nth victim of the Floating Point arithmetic syndrome

Perhaps try =IF(OR(G4<>"",J4<>"",Q4<>""),round(M4-B4,15),"") to solve it

[Crackerdaq]

Thank you sir. Thought it was probably that.........

Anyways - sorted!

[Crackerdaq]

In my simple world, it is finding the time difference between M3 (7:30) and B3 (7:30) The answer should be 0:00.

It is then doing the same calculation between M4 (5:30) and B4 (5:30). The answer should also be 0:00. I don't get why it is displaying N4 as a negative. Same for column O as a negative and red.

I am sure there is a perfectly reasonable explanation but how do i make it look as it should!

[judgeh59]

right....but the world isn't simple....it's a nasty complex strange Microsoft world....and the resolution of subtraction is on the order E-17....that's 17 zero on the right side of the decimal point...Mokko fix should resolve the issue....

[judgeh59]

glad we help....

@Mokko - Nice fix!!!

[Pepe Le Mokko]

I am sure there is a perfectly reasonable explanation

Well , Excel is a MS product, and the day MS software will be perfectly reasonable, I'll be long gone

[judgeh59]

@Pepe le Mokko -