Google-sheets – Calculate the amount paid according to time in/out with an optional break in between

You can do this with the DSUM function. The general form is:

=dsum([data range], [column to sum], [criteria])

[data range] would be the data in your Worksheet B, something like 'Worksheet B'!A:C where 'Worksheet B' is the actual name of the worksheet.

[column to sum] is the header of the column that you want to add up. In your case, that would be "Hours Spent".

[criteria] is the complicated part. You need to define the criteria for selecting which rows to sum. In your case, The total hours spent for "B1" is the sum of hours spent for each row where BlockID = "B1". You actually define this criteria in another place in your spreadsheet.

I recommend you create a new sheet named Criteria. On this sheet, in A1 put BlockID and in A2 put ="=B1". I know if looks funny, but you need to type it just like that.

(It occurs to me that you might run into trouble where your BlockIDs collide with cell references.)

Now the full formula for the sum of all hours spent on B1 is:

=dsum('Worksheet B'!A:C, "Hours Spent", Criteria!A1:A2)

For each new block ID, add another set of criteria to the Criteria sheet and modify the formula as necessary.

İmporting the another workbook you should learn its key like below:

=dsum(importRange("spreadsheet_key";'Worksheet B'!A:C), [column to sum], [criteria])

Try:

=mod(C2-B2,1)

and drag down as far as needed. Make sure the format the cells as duration. For the 'total time' a simple sum should suffice.