# How to calculate hours missing (a number minus a duration)

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?

## 1 Answer

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

• 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 '16 at 12:39