Solved Is there another way for Excel to show negative times

wiganken

Member
Member
Messages
106
Because Excel cannot show negative times (except as an infinite number of ## characters) I have devised a way for Excel to show that times (hh:mm) are negative by inserting a column that shows "Exceeded" before the "Exceeded By" value. The attached picture shows what I have done. Is there a better or easier way to show that cell B2 exceeds A2?
 

Attachments

  • Exceeded.png
    Exceeded.png
    15.4 KB · Views: 127
You can convert the time to an integer, and then subtract one integer from the other. Since integers are either positive or negative, if you display the result as an integer; it will be either positive or negative, which will accomplish your purpose. Or, convert the result back to time format; and if it was negative, include the word "Exceeded" in what is displayed.
 
By integers I think you mean converting the hh:mm to a decimal number so 15:45 (hh:mm) becomes 15.75? I don't mean to be pedantic but 15.75 is not an integer because, by definition, integers have to be whole numbers (+ve or -ve). I do get your point but I think it is likely to be more complicated than my method. Thanks for your input. All help is appreciated.
 
You can specify TIMEVALUE(15:45) to get an integer equivalent for the time. You can compare TIMEVALUE(A2) to TIMEVALUE (B2) to see which is greater, and make your decision based on that.
 
It converts times into decimal parts of the day and gives the answer in same format so not what I was after. I wanted to stick with the standard hh:mm time format for simplicity. I think I will stick with my original idea but thanks for the input (you are the only one who tried to help :) ). I'll call this thread closed now and stop following it.
 
Back
Top