calculate positive and negative times in excel 2003 (new) - Super User

possible duplicate:
calculate time differences in excel 2003

i have goal of 2h47m00s minutes per month. if attend let 1h20m00s , when excel cell format [red]-[m]:ss;[blue][m]:ss gives me in red color 87 minutes time difference before reach goal. when it's negative gives me right answer. if reach 3h00 should give me 13 minutes colored blue, doesn't. gives me ################# answer. can me right formula. thanx , not problem of widening cell.

say want record jogging times , set objective of 2h47 hours of jogging per month (you precise person).

you'll need few cells...

• cell a1 (let's call goal).

type in 2:47, objective jogging time. cell going take custom format of h:mm, hours:minutes, in reality excel stores "time" date , time value. in case, january 1st, 1900, 2:47 am, don't care that. although convenient when keying in data, need take account when doing our formula.

• cell a2 (let's call achieved)

type in formula: =sum(c1:c50). total of jogging times going enter during month in column c. there's no problem in adding positive datetimes, since never below zero.

• cell a3 (let's call monthdifference)

here's catch. excel under article "present result based on single time unit" suggests:

=int((b2-a2)*24) total hours between 2 times (4)

=(b2-a2)*1440 total minutes between 2 times (295)

=(b2-a2)*86400 total seconds between 2 times (17700)

i've picked minutes, timing jogging seconds might bit excessive. in a3, type =(a2-a1)*1440. set a3 cell custom format this: [red]-0;[blue]0.

voilà! have go run outside , come write times in cells c1 c50. luck.