1

I've calculated total hours worked in a google sheet. Lets say total hours worked appear in column 'Q'. In the next column over, column 'R', I need to calculate the total hours missing and I'm not sure how to do this.

So for example, say total hours worked is 7:30 (currently formatted as a duration in cell Q2), in R2 I entered:
=(8-Q2), which is a number minus a duration. The result was way off.

Anyone know how to do this?

Improve this question

1 Answer 1

Reset to default
1

Dates and Times in all spreadsheets including google spreadsheets is stated in days. Twelve hours is a half a day.

If a duration is 7:30 that means that the number in the cell is 7.5/24 or 0.3125

The 8 in your formula isn't 8 hours, the spreadsheet thinks it is 8 days or 192 hours. To get the correct answer make the formula: =(8/24)-Q2

Improve this answer
1
  • thanks! that worked great. one issue arose though. in the event that someone worked more than 8 hours the result is negative, which is technically correct. However, practically, this is not usefully. Any ideas on how to fix this?
    – James
    Sep 13, 2016 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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