Google Sheets – Subtract 8 Hours from Time if Time Exceeds 8 Hours

You can do this by modifying the current formulas and adding one of your own.

Based on the template you mentioned, you need to amend the formula for the Total hours per day: column to:

=IF(24*(C2-B2) > 10, 10, 24*(C2-B2))

Then in Column E add a new formula:

=IF(D2=10, ABS(10.00 - (24*(C2-B2))),0)

Then the Formula for Total Hours For This Pay Period needs to be amended to include any hours over the 10 hour threshold.

=SUM(D2:D16)+SUM(E2:E16)

This will give you an output similar to the this:

I'll outline the strategy in three steps. Some of them may be combined, but doing things step by step makes the process more manageable.

Import ranges

Start by pulling the data from assorted spreadsheets into one summary spreadsheet. For example, create a separate sheet/tab for each source, enter =importrange(..., "A:E") in A1, and something like

=arrayformula(if(A:A <> "", {"This_Client", "This_Category"}, ))

to mark the client and category for the source.

Combine data

In a summary sheet, enter

={filter(Sheet1!A:G, len(Sheet1!A:A)); filter(Sheet2!A:G, len(Sheet2!A:A)) } 

to combine all the sources, excluding empty rows.

Query data

Use a query to summarize data and present selected columns.

=query(Summary!A:G, "select A, sum(B), F, G group by A, F, G")