microsoft excelmicrosoft-excel-2003windows xp
I have a goal of 2h47m00s minutes per month. If I attend let's say 1h20m00s and when my Excel cell format is:
[Red]-[m]:ss;[Blue][m]:ss
…then it gives me in red color 87 minutes time difference before I reach my goal. When it's negative it gives me the right answer. But if i reach 3h00 normally it should give me 13 minutes colored blue, but it doesn't. It gives me ################# as an answer. It's not a problem of widening the cell.
Can someone help me get the right formula?
I've found a work-around that doesn't change the font color, but effectively removes the text from the cells. Adjust the cell number format to Custom, with a value of ;;;.
The problem using DATEDIF is it doesn't calculate negatives. Microsoft states:
Displays a #NUM! error because start_date occurs before end_date (#NUM!)
Therefore, the rule doesn't run as you entered it. Since Excel recognizes dates, use the simple function of SUM on this and it works as you described.
=SUM($N$9-N12)>=3 for green
=SUM($N$9-N12)=2 for yellow
=SUM($N$9-N12)<2 for red
Here are the rules.
And the results.
NOTE: My date format is m/d/yyy. This was done in 2010, but should be the same to 2013.
Best Answer
The ### are coming from Excel and negative time values.
Take a look at: