calculate positive and negative times in excel 2003 (new)

Question

Possible Duplicate:
calculate time differences in excel 2003

i have a goal of 2h47m00s minutes per month. if i attend let say 1h20m00s and when my excel cell format is [Red]-[m]:ss;[Blue][m]:ss 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. Can someone help me get the right formula. Thanx And its not a problem of widening the cell.

Answer 1

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.