I use a Google Docs spreadsheet to keep track of the time I spend on projects. It's formatted in 3 columns: the time I started, the time I finished, and the total hours spent for that session (represented as a decimal).

``````+---+---------+----------+---------------+
|   |    A    |    B     |       C       |
+---+---------+----------+---------------+
| 1 | Started | Finished | Hours/Session |
| 2 | 9:30    | 11:00    | 1.5           |
| 3 | 9:45    | 12:00    | 2.25          |
+---+---------+----------+---------------+
``````

Right now I'm using my fingers (which is ridiculously un-poweruser like) to add up the total number of hours from start to finish. Is there a way to do this automatically?

It may be a little late, but here is how I do it:

``````=HOUR(B2-A2)+(MINUTE(B2-A2)/60)
``````

Basically, being B2 the end time and A2 the start time, you subtract B2-A2 and get the hour from the subtraction. Then you do the same and get the amount of minutes. After you have both, you sum the amount of hours with the amount of minutes divided by 60 (to get it in hours).

Don't forget to use the 24-hour format (i.e. 10:00 or 22:00).

Google Sheets now have a duration formatting option. Select: Format -> Number -> Duration.

Now-a-days one can use the `ARRAYFORMULA` as well, to save some time:

`=ARRAYFORMULA((HOUR(B2:B-A2:A)+(MINUTE(B2:B-A2:A)/60)))`

See example file: Hours

You can use a formula:

1. Type this in the first cell of `hours/seesion`: `=(B2-A2)*24` (assuming A2 is the starting time and B2 the end time)

2. Click in the right corner of that cell and drag it down the whole column.

3. Select the whole column (click on the column letter)

4. click on `123` in the toolbar

5. click on `Normal`

• Everything worked except the formula, which produces negative numbers Commented Aug 16, 2010 at 13:35

given `A: start time` and `B: end time` i use this:

``````=(IF(A2 < B2; 0; 1)+B2 - A2) * 24
``````

the `if` part is needed to calculate day-switch correctly, eg. you start at 10:00 and work till 02:00 (the other day), you would end up with with `-8` hours instead of `14`, bad for business i'd guess.

• This formula didn't produce negative numbers, but the resulting numbers were still wrong Commented Aug 16, 2010 at 13:35
• I liked this one best, but I used `=(IF(A2 <= B2; 0; 1)+B2 - A2) * 24` (note the `<=` at the beginning) so that start and end times that matched (and empty cells) would be zero instead of 24. Commented Nov 15, 2012 at 15:37

Be aware that your 'time cells' are formatted the right way!

I just learned that google docs with Danish settings will need time formatted like this:

``````+---+---------+----------+
| 1 | Started | Finished |
| 2 | 9.30    | 11.00    |
| 3 | 9.45    | 12.00    |
+---+---------+----------+
``````

cinaglia's working formula above only works when google docs spreadsheet recognizes the cells as (time) numbers. I guess this is probably logic for beginners but I looked around a WHOLE LOT before I changed the 'colons' to 'periods' - and got the formula working (thanks cinaglia).

wow people... =text(end time cell- start time cell, "h:mm") BUT now am finding this string bc google sheets can not add up the clean hours column. Help. I MISS EXCEL SO MUCH!!!