mardi 1 octobre 2019

Need to count how many specific hours are within 2 timeframes

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