Google Docs Time report - How to add hours?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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!!!