Google Docs Time report - How to add hours?

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).

• It's not too late and I've just been doing it manually. This formula works, I just need to get used to using 24-hour formatting now. – T. Stone Jan 4 '11 at 21:27

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 – T. Stone Aug 16 '10 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 – T. Stone Aug 16 '10 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. – jocull Nov 15 '12 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).