Google Sheets – Calculate Difference Between Two Time Fields

dateformulasgoogle sheetsgoogle-sheets-dates

I'm having trouble getting negative hour as a result. For instance:

G           H
08:30       06:00

I would like to get a column with the difference between H and G. So I would be filled with -02:30.

When I do a simple calculation, like G2-H2, I get 22:00 as a result.

Any ideas on how to fix this?

I saw this post How to handle Negative Time Delta in Google Spreadsheets, but none of them have the solution I'm looking for.

Best Answer

You mention

I would like to get a column with the difference between H and G. So I would be filled with -02:30.

The formula =H2-G2 will work fine in Google Sheets once you format as Duration by going to
Format - Number - Duration

Related Solutions

Google Sheets – Query Syntax Using Cell Reference

Please try syntax of this kind:

=QUERY(Responses!B1:I, "Select B where G contains '"&$B1&"'")

Google-sheets – How to calculate difference in hours between two time fields which runs from one day to the next

As user3169 wrote, one can simply take the difference of time values (this works in Google Spreadsheets the same as in Excel). Specifically, the command

=ROUND((F2-E2)*24)

calculates the number of hours between the time values in the cells E2 and F2, rounded to the nearest whole hour. Similarly,

=ROUND((F2-E2)*24*60)

calculates the number of minutes between the time values in the cells E2 and F2, rounded to the nearest whole minute. Etc.

Best Answer

Related Solutions

Google Sheets – Query Syntax Using Cell Reference

Google-sheets – How to calculate difference in hours between two time fields which runs from one day to the next

Related Topic