Google-sheets – How to calculate hours missing (a number minus a duration)

google sheets

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?

Best 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

Solution

Suppose total distance is in column C1 and total time is in D1 then average speed formula would be:

=C1/(int(D1)*24+hour(D1)+minute(D1)/60+second(D1)/3600)

int function is the one I was after.

Google Sheets – How to Use an IF Statement Over Several Cells

This is best done like this:

Formula

=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A")
=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A Label SUM(B)'Units'")

Explained

If you include headers, then the QUERY function will add them as such and alter them accordingly, ie. SUM title or COUNT title....etc. You can also added a custom label to the calculated columns, see 2^nd example (3^rd sheet). The rest of the function will perform as standard SQL.

Example

I've created an example file for you: sum over several cells

Best Answer

Related Solutions

Google-sheets – Parsing Time data in Google Spreadsheets

Solution

Google Sheets – How to Use an IF Statement Over Several Cells

Formula

Explained

Example

References

Related Topic