-3

I have 7 columns in a Google Spreadsheet:

  • A) Timestamp B) Check-in time C) Break time (start) D) Break time (end) E) Check-out time F) Hours worked G) Name

Data is being imported from form responses into columns A, B, C, D, E and G.

How do I make column F display the result of row E minus B while calculating the break time duration from row C-D? Better said: How do I see the hours worked from check-in to -out while deducting the break time?

I've attached a copy of the spreadsheet tab for reference. https://docs.google.com/spreadsheets/d/1GI0T85CQwLgrR4MmLZfn6ZNP4azJRcmiftahvkQCVps/edit?usp=sharing

Any input would be greatly appreciated!

techinzurich
  • 13
  • 2
  • This formula should do the trick: `= F1-B1 - (D1-C1)`. Change the number to match the row. However, your question shows very little research effort, or it is not clear what you are asking. This is probably the reason why you have received so many negative comments. For the future, we encourage you to show what effort you spent before you dropped the ball and asked for help. – Daemon Painter Mar 20 '20 at 15:31
  • Does this answer your question? [how to calculate difference between these two times in excel](https://stackoverflow.com/questions/40404808/how-to-calculate-difference-between-these-two-times-in-excel) – Daemon Painter Mar 20 '20 at 15:33
  • See https://meta.stackoverflow.com/questions/261592/ – TheMaster Mar 20 '20 at 16:16

1 Answers1

0

You should first do the difference between the checkin time and the checkout time and then remove the break time. To compute the break time you can use the same logic we used for the first difference. So it would be:

HoursWorked = CheckoutTime - CheckinTime - (endBreakTime - startBreakTime);

='E1'-'B1'-('D1'-'C1')
Alessandro
  • 2,848
  • 1
  • 8
  • 16