What is the formula to use for hours worked in Google Sheets?

Question

excel

Need a formula to display time as a numeric value for counting hours worked by employees. For ordinary shifts, I found the formula:
=TO_TEXT(A2-A1)*24
where A1 \u003d shift start
A2 \u003d shift end
Here the question is with night shifts, they are counted as an hour and a half per hour worked. How can I add a range of hours to the formula so that, for example, from 22:00 to 06:00, instead of 8 hours, 12 hours would automatically be indicated? And if the shift is from 20:00 to 04:00, then 11 would be indicated.

Answer 1

The easiest way is to multiply everything at once ( start_shift - end_shift ) by 1.5 , and then subtract 0.5 from what is included in the range of the day shift (how to calculate it - below), so you can get by with one range, not a pair. Or simply consider the day range, and get the night range as the difference between everything and the day , for the same reasons.
And what to take away - there are several options.
1) Fully falls within the daily range. Those.
( start_shift >= start_day ) AND (end_shift <= end_day )
take into account ( end_shift- start_shift )
2) Partially hits the left. Those.
( start_shift < start_day ) And ( end_shift <= end_day )
take into account ( end_shift - start_day )
3) Partially hits the right. Those.
( start_shift => start_day ) And ( end_shift > end_day )
take into account ( end_day - start_shift )
4) Completely closes the day
( start_shift < start_day) And ( end_shift > end_day )
take into account ( end_day - start_day )
All that remains is to collect everything in the IF () group. 4 conditions, i.e. total 3 nested IF ()
DEMO table