Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

The ### are coming from Excel and negative time values.

Take a look at:

  1. Is there any way to properly display negative time spans in excel
  2. Display Negative Time Values
share|improve this answer

Say you want to record your jogging times and set yourself an objective of 2h47 hours of jogging per month (you are a precise person).

You'll need a few cells...

  • Cell A1 (let's call it Goal).

Type in 2:47, which is your objective jogging time. The cell is going to take a custom format of h:mm, for hours:minutes, but in reality excel stores any "time" as a date and time value. In this case, January 1st, 1900, 2:47 AM, but we don't care about that. Although convenient when keying in your data, we will need to take that into account when doing our formula.

  • Cell A2 (let's call it Achieved)

Type in this formula: =SUM(C1:C50). This is a total of all your jogging times that you are going to enter during your month in the column C. There's no problem in adding positive datetimes, since they will never get below zero.

  • Cell A3 (let's call it MonthDifference)

Here's the catch. The excel help under the article "Present the result based on a single time unit" suggests:

=INT((B2-A2)*24) Total hours between two times (4)

=(B2-A2)*1440 Total minutes between two times (295)

=(B2-A2)*86400 Total seconds between two times (17700)

I've picked minutes, as timing jogging seconds might be a bit excessive. In A3, just type =(A2-A1)*1440. Then set your A3 cell custom format to this: [Red]-0;[Blue]0.

Voilà! Now you just have to go run outside and come back to write your times in cells C1 to C50. Good luck.

share|improve this answer
    
Actually it's not my monthly jogging time, it's sillier than that... It's the amount of time that i get phone calls on my cell on a monthly basis ! My goal is 2 hours 47 minutes and 00 seconds per month which i typed in cell A1 as 2:47:00 . What i'd like to find out is let's say i type in cell B1 1:47:30 (format= h:mm:ss) worth of phone calls it should answer me 0h59m30s in cell C1 or 0:59:30 in red (negative) that is before i get to the 2h47m00s goal. On the contrary if i get 3h48m30s worth of phone calls it should answer me in cell C1 1:01:30 (format= h:mm:ss) in blue (positive).. –  mary Jan 13 '10 at 11:31
    
I am pretty sure that's exactly what I've written, typing 2:47 at both places gives a result of zero. Have you tried it? –  mtone Jan 13 '10 at 16:10
    
I should mention that I made it for minutes, not seconds. sorry. –  mtone Jan 13 '10 at 18:23
    
thanks monotone, but seconds was the essential part of my problem! how can i use mm:hh:ss in a cell A2 and get the answer i'm waiting for in cell A3, with 02:47:00 in cell A1 ? I've tried your formula but i can't differentiate the "ss" part of A2 so that A3 gives me the right answer............................................example: A1=02:47:00 (goal) A2= 01:22:52 (my time) A3= 0:24:08 (A1minusA2) in red if its below goal or blue if over goal. Thanks again –  mary Jan 15 '10 at 15:16
    
i havent found the answer yet, so for now i discarted the seconds and it works. Thanx a lot –  mary Jan 21 '10 at 17:43

The problem is that you are attempting to display an excel cell that contains a negative value and that is formatted as time.

If you change the format of the cell to a decimal number you will see the time displayed correctly, but as a negative fraction of one day.

87 minutes is 0.0604 of a day

13 minutes is 0.0090 of a day - it is this negative value that is giving you the problem when the cell is formatted as time.

To display minutes, multiple this faction by 1440 (the number of minutes in a day). When you have done that, you will see that the number displays positive and negative minutes correctly.

Having done that, you should find it easy to display red and blue minutes for negative and positive values; the correct cell format is :

[Blue]#,##0.00_);[Red](#,##0.00)
share|improve this answer
    
Are you sure that format is correct? I'm no Excel user, but it looks funny to me. I even checked the Markdown source, but it's really what you typed... (By the way, you can use <pre> .. </pre> to avoid the odd syntax highlighting.) –  Arjan Jan 12 '10 at 21:22
    
@Arjan - The format was copied/pasted from the Excel cell format dialog. You can paste it back into the Excel format cells dialog box (Format cells/number tab/Custom option) to see the result. I did test it successfully using Excel 2003 SP3. I did have a little trouble pasting it into my answer; paste as code snippet seemed to work best. Regards, Kije. –  Kije Jan 12 '10 at 23:09
    
... and thanks for the <pre> tip. Regards, Kije. –  Kije Jan 12 '10 at 23:20

You may verify what you're doing against this Microsoft article:
Calculate the difference between two times.

If that doesn't give any answers, it would help to know what formulas you're using.

share|improve this answer
    
here's my formula: [Red]-[m]:ss;[Blue][m]:ss –  mary Jan 11 '10 at 14:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.