Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

6 Answers 6

up vote 6 down vote accepted

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

share|improve this answer
    
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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
1  
This formula didn't produce negative numbers, but the resulting numbers were still wrong –  T. Stone Aug 16 '10 at 13:35
1  
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

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

share|improve this answer
    
Everything worked except the formula, which produces negative numbers –  T. Stone Aug 16 '10 at 13:35
    
@T.Stone Can you clarify please? This formula gives me the same results for your sample as the (longer) formula in the accepted answer. I have A and B formatted as HH:mm . –  pnuts May 7 at 15:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.