I'm trying to make a time registration for employees. I have made a Google Forms that the employees will register start date&time and end date&time in this format:
Employee X | 10/6/2019 12:00:00 | 10/6/2019 20:00:00
This is 3 cells in Google Sheets.
The employees need to get payed x more when they work between the hours 18:00 & 06:00 in the morning. So I need a formula that takes the start time and end time of their shift and counts how many of the shift hours that fall between 18:00 & 06:00 and returns the amount of hours that they have worked in this frame.
In the above example, Employee X has worked from 12:00 til 20:00, an 8-hour total workday.
BUT the formula needs to return how many of those hours that are between 18:00 & 06:00 eg. 2 hours. in a cell of its own.
I have tried all sorts of COUNTIFS
, FILTER
and other stuff, but this one I cannot solve.
Does anyone have the magic touch?
Aucun commentaire:
Enregistrer un commentaire