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).
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:
Voilà! Now you just have to go run outside and come back to write your times in cells C1 to C50. Good luck.