Huge nested IF or is there a simpler way?

[Ramballah]

Hi,

I am making this post because I think I have been looking at something the wrong way.

I have this schedule/planning sheet where I plan my people with the shifts they need to work. And I figured out how to count the hours they work with the system I use.
Though not entirely. I tried asking around before but no progress has been made so I kept trying and looking up and maybe I am just thinking about it wrong!

Anyway, the only shift I have problems with are my sleep shifts. They consist of 2 days as you need to be there after midnight obviously. I was first looking into matching pairs of "4" but no success. I tried countifs, ifs etc nothing worked. So here I am asking if atleast someone knows what would be possible or atleast the right direction.
What my current theory is is that I just countif(C10:AM10,4) and then divide that by 2 and then times 16 (as the shift is 16h). But then I have 2 issues.
Here is my current formula first (this is without the x16 to make it simpler.

=INT(COUNTIF(C10:AM10,4)/2)

which returns 5 right now which is almost there.
1. The shifts can end on the first of the month (looking at D10) and also start on the last day of the month (AH10). Right now its fine as there is one ending on the 1st and on beginning on the 31st making it 1 full shift. And if there is only 1 beginning or ending the formula returns "4", which is correct as there are only 4 full shifts. But I still need to add that half one up. Then comes the issue, these days can shift around with each month and year cause of a formula. Row 6 is stationary (C6=sunday and AM6=sunday, just with conditional format its hidden) but row 5 changes with the month / year due to calendar formula. Meaning that if I want to find that single 4 in the beginning or end of the month, it can take like 7 if formulas for the beginning 4 if not more and same for the last 4? Maybe there's something with checking row 5? like

if(and(C10=4,C5=1)=true,6,if(and(D10=4,D5=1)=true,6,

etc etc going on... i just dont know how to make it all fit (also the 6 is for 6 hours, as 10hours happen on day 1, 6 on day 2)
2. And problem 2 are the weekends (Za/Zo which is Sat/Sun). When the 4 ends on either one of the days, the shift is 16,5h and not 16h.. I think this one is simpler just requires a lot of if's too. Was thinking something like

=IF(COUNTIFS(H10,4,I10,4)=1,0,5,0)+IF(COUNTIFS(I10,4,J10,4)=1,0,5,0)-IF(OR(COUNTIFS(H10,4,I10,4,J10,4,K10,4),COUNTIFS(G10,4,H10,4,I10,4,J10,4))=TRUE,0.5,0)

This formula works, but its for just the one weekend.. So i'd need to copy this for 4 more weekends making it extremely long. It works, but wondering if its maybe possible to do it in a smaller way. (also the 0.5 output is just straight up the 0,5 needed to make 16,5 instead of 16h etc)

So yeah was wondering how to tackle problem 1 and if problem 2 has a simpler way
Sorry if this is a big problem, sorry if theres not enough info to go on. Just let me know if u need more clarification!
Thanks in advance,
Ramballah

 

[offthelip]

you can count the number occurrences of 2 adjacent 4s using this equation:

=COUNTIFS(D10:AH10,4,E10:AI10,4)

I am not sure what to do about the other bits

 

[Ramballah]

you can count the number occurrences of 2 adjacent 4s using this equation:

=COUNTIFS(D10:AH10,4,E10:AI10,4)

I am not sure what to do about the other bits

Well if I were to use this, I still am left with the 2 issues I have. How do I find the single 4 in the beginning of the month and at the end of the month if they are there (its dynamic so it can be in any column between C and I or AG and AM). And I need to take the weekends separate because they have a +30 minute shift, hence my formula in issue 2 plusses a 0.5.

 

[Ramballah]

Well, im going with this to tackle issue 2:

=IF(COUNTIFS(H10;4;I10;4)=1;0,5;0)+IF(COUNTIFS(I10;4;J10;4)=1;0,5;0)-IF(OR(COUNTIFS(H10;4;I10;4;J10;4;K10;4);COUNTIFS(G10;4;H10;4;I10;4;J10;4))=TRUE;0,5;0)+IF(COUNTIFS(O10;4;P10;4)=1;0,5;0)+IF(COUNTIFS(P10;4;Q10;4)=1;0,5;0)-IF(OR(COUNTIFS(O10;4;P10;4;Q10;4;R10;4);COUNTIFS(N10;4;O10;4;P10;4;Q10;4))=TRUE;0,5;0)+IF(COUNTIFS(V10;4;W10;4)=1;0,5;0)+IF(COUNTIFS(W10;4;X10;4)=1;0,5;0)-IF(OR(COUNTIFS(V10;4;W10;4;X10;4;Y10;4);COUNTIFS(U10;4;V10;4;W10;4;X10;4))=TRUE;0,5;0)+IF(COUNTIFS(AC10;4;AD10;4)=1;0,5;0)+IF(COUNTIFS(AD10;4;AE10;4)=1;0,5;0)-IF(OR(COUNTIFS(AC10;4;AD10;4;AE10;4;AF10;4);COUNTIFS(AB10;4;AC10;4;AD10;4;AE10;4))=TRUE;0,5;0)+IF(COUNTIFS(AJ10;4;AK10;4)=1;0,5;0)+IF(COUNTIFS(AK10;4;AL10;4)=1;0,5;0)-IF(OR(COUNTIFS(AJ10;4;AK10;4;AL10;4;AM10;4);COUNTIFS(AI10;4;AJ10;4;AK10;4;AL10;4))=TRUE;0,5;0)

But I still need something for issue 1, for finding the first and last 4.. I hope someone has some helpful insight

 

[Ramballah]

bump