Finding the first certain value with conditions

[Ramballah]

Hi,


I am looking for a formula that can find the first certain value if its there that also has certain conditions met.
First let me show you a simpler version of my spreadsheet:

So what I need is to find the very first "4" in the row that does not have one next to it, and the last "4" that does not have one next to it. I just need this per row, so not for all 4 rows in 1 formula, just 1 formula for 1 row.
But the issue comes as follows:
1. As you can see, the first 4 starts on column B which is the first of the month, However this can change dynamically from any column: A,B,C,D,E,F and G. Since not every month starts on the monday
2. As you can see, the last 4 is on column AF which is the last date of the month, However this can change dynamically from any column: AE,AF,AG,AH,AI,AJ and AK. Since not every month ends on a wednesday.
3. I only need to find this 4 if its on the 1st of the month or on the last day (28, 29, 30 or 31) of the month
4. The "4"'s need to be a single 4, and not paired up like B3:C3 or AE4:AF4
5. It can also happen that there is not a single 4 on the first or last day but just empty. That's fine I don't need those and I don't need the pairs.
6. If it's any important, there are other values used in the cells aswell, so isblank is not gonna be helpful.
So for row 3 there is one "4", row 4 has one "4", row 5 has one "4" and row 6 has none.
If it gives a clarification for someone: 4's are always paired, and the month ends for example on the 31st, then in the next month the other 4 will be on the 1st making it a pair. So I just need to find those 4's that are at the end/beginning of the months.

I hope someone can finally help me with this!

Thanks in advance,
Ramballah

 

[mumps]

It is hard to work with a picture. If you are open to using a macro, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I only need to find this 4 if its on the 1st of the month or on the last day (28, 29, 30 or 31) of the month

What do you want to do with the 4 when you find it?

 

[Ramballah]

It is hard to work with a picture. If you are open to using a macro, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

What do you want to do with the 4 when you find it?

I have attached this sheet below for you. It's not the original sheet I use since that one would just complicate things in my opinion. It doesn't matter where you place the formula, since I can place it in the right places in my original sheet if it works lol. What I need is to either return a true, or 1. Since I need this for monthly hour totals. If the 1st of the month is a 4 it means its a 6h that needs to be added to the totals. If its at the end of the month so the 31st, its a 10h that gets added. So with a true or 1, I can translate that to the right hours. If you do need the original sheet, I will post it in the next answer but again I think it might make it harder!

Rooster hof van hersbeek.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
2	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
3		4	4																													4
4		4																													4	4
5		4																														4
6		4	4																												4	4
Sheet1

 

[Ramballah]

The formula in B22 is what counts the total hours of all the shifts for just row 10 except for nr4. The other 2 formulas around there are for making the total calculation for shift 4. But I am missing the part that I asked here in this page.

Rooster hof van hersbeek.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1		Rooster Hof van Hersbeek																																2024
2
3			1	Dag 9.30-15.30					2	Avond 13-21				3	Avond 14-22*				4	Slaap*			5	Vergadering				x	Verlof			v	Vakantie
4
5		January 2024		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
6			Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma
7		Corine		1	2	3	4	5	x	v
8		Jaco
9		Juanita
10		Michelle		4					4	4						4	4							4	4				4	4				4
11		Mila
12		Mireille
13		Ramon
14		Ruben
15		Rutger
16		-
17		-
18		-
19		Charlotte
20		Miranda
21		5,0	*Slaap diensten zijn van 15.00 tot 10.30 alleen zaterdag en zondag zijn tot 11.00 en de avond diensten zijn dan 15-22
22		0	0,5
Januari

Also im unsure what happened with the column sizes.

 

[mumps]

Please note that I have offered a macro solution not a formula solution. If you are willing to use a macro, then please clarify if your actual data is organized as in Post #3 or Post #4.

If the 1st of the month is a 4 it means its a 6h that needs to be added to the totals. If its at the end of the month so the 31st, its a 10h that gets added.

Where are the totals found on your sheet? What happens if both the 1st of the month and the end of the month are both single 4's as in row 5 of the sheet you posted in Post #3?

 

[Ramballah]

Please note that I have offered a macro solution not a formula solution. If you are willing to use a macro, then please clarify if your actual data is organized as in Post #3 or Post #4.

Where are the totals found on your sheet? What happens if both the 1st of the month and the end of the month are both single 4's as in row 5 of the sheet you posted in Post #3?

If that happens, its just 1 entire shift so 15,5 hours or 16 if it the first day is a sun or sat.
If possible I'd prefer the entire total calculation to be macro, or just formula's. I have little understanding of VBA so I opted for formula's as I can get around with that. I was planning on having the total's merged with the names of the people in column B. For example: "Michelle - 58,5" but have the total calculation just be hidden in row 1 or 2 with white text. If you think it's better to do the entire month calculation in VBA then I shall say how to calculate it:
Shift 1 = 5,5 hours but if its on a saturday or sunday its 6 hours.
Shift 2 = 7,5 hours
Shift 3 = 7,5 hours but if its on a saturday or sunday its 6,5 hours
Shift 4 = 15,5 hours but if it ENDS on a saturday or sunday its 16 hours. (What I mean is that shift 4 is a night shift so it goes onto the next day so it always gets pair, 4 on a starting day and 4 on the ending day. So if the shift starts on friday, it will end on saturday, and since it ends on saturday its a 16h shift instead of 15,5h.
And if shift 4 ENDS on the very first day of the month (meaning it began on the end of the previous month) it's 5,5h or 6h depending on the sat/sun part. And if it ends on the last day of the month its 10h.
So If I take this it will be:

Rooster hof van hersbeek.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
5	January 2024		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
6		Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma
7	Corine		1	1	1	3			4	4		1	1	2	3			2	2	2	1	1		4	4			4	4			1	4
Januari

8x shift 1 = 8*5,5 = 44 but 1 shift was on a saturday so its 44,5 (1 shift was 6h)
4x shift 2 = 4*7,5 = 30
2x shift 3 = 2*7,5 = 15 but 1 was on a saturday so its 14
3x shift 4 = 48 but 1 ended on a saturday so its 48,5
1x begin shift of 4 = 1x 10 since it begins on the 31st.
Meaning the total hours for this person is 44,5+32+14+48,5+10 = 147h.
I hope this explains it.

 

[mumps]

I think it might be easier to understand if you post actual data for 5 or 6 people and include your expected results in row 1 or 2.

 

[Ramballah]

I think it might be easier to understand if you post actual data for 5 or 6 people and include your expected results in row 1 or 2.

I have here one sheet fully filled in. As you can see I put the total hours in column B with their names. As we do it by hand normally right now. It follows all the rules I mentioned in post #6.
I hope this clears it up! Also sorry for the column widths, i dont know why its like this

Rooster hof van hersbeek.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1		Rooster Hof van Hersbeek																																2024
2
3			1	Dag 9.30-15.30					2	Avond 13-21				3	Avond 14-22*				4	Slaap*			5	Vergadering				x	Verlof			v	Vakantie
4
5		January 2024		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
6			Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma	Di	Wo	Do	Vr	Za	Zo	Ma
7		Corine - 55,5		1		3		1	4	4	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	1	1	4
8		Jaco - 101,5					2				1	2			1	3			2	3		2	3		1	1	3			3		3		2
9		Juanita 133,5			3		1	2			2	3		1	4	4		3	3		2		4	4	3	2	x	3	x	x	x	x	4	4
10		Michelle - 149		2	4	4	x	x	x	4	4	4	4		3		4	4			1	4	4	1		4	4		4	4	3	x	3	x
11		Mila - 49,5		4	1	4	4		v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	v	4	4	2
12		Mireille - 142,5		4	4		4	4			3		1	4	4		1	2	1		4	4		3	x	x	1	4	4	x	x	4	4	1
13		Ramon - 134			2	2	3		1	1	4	4	2	3				4	4	2		1		4	4	3			1	1	1	v	v	v
14		Ruben - 137,5				2		4	4				3	2	2	4	4			4	4	3	1		x		4	4	3	4	4	x	x	3
15		Rutger - 90		3		1		3	3	3		1	4	4		1	3		4	4	3		v	v	v	v	v	v	v	v	v	v	v	v
16		-
17		-
18		-
19		Charlotte - 11																1		1
20		Miranda - 13																							2			1
21			*Slaap diensten zijn van 15.00 tot 10.30 alleen zaterdag en zondag zijn tot 11.00 en de avond diensten zijn dan 15-22
22
Januari

 

[mumps]

There are two single 4 cells in the sheet: D11 and AH7. Can you explain in detail, referring to specific cells, rows, columns and ranges, how you use these two cells to calculate the totals in column B? Two or three examples should be enough.

 

[Ramballah]

There are two single 4 cells in the sheet: D11 and AH7. Can you explain in detail, referring to specific cells, rows, columns and ranges, how you use these two cells to calculate the totals in column B? Two or three examples should be enough.

So with the 4's. if its on the end of the month, I add up 10 hours. Since the total shift is 15,5 or 16 hours (Depending on whether it ends on a weekend day or weekday). When the 4 is single on the beginning of the month, I take the 5,5 or 6 hours of that said shift. Since its a new day, past midnight. So a regular shift 4 is made up of two 4's since it goes onto the next day, the total is 15,5/16hours and with the single 4's I just split that.
For the example:
Row 7:
There are four 1's so 4x5,5 = 22h (none of them were on a weekend)
There is a single 3 so 1x7,5 = 7,5h (not on a weekend)
There is a single pair of 4, but it ends on a sunday (J7) so thats 1x16 = 16h
There is a single 4 at the end of the month (AH7), meaning its the first part of the shift which is 10h. So 1x10 = 10h (if it was the first 4 of the month it would be 5,5h or 6h)
Together 22+7,5+16+10 = 55,5h

Row 8:
Four 1's = 4x5,5 = 22h (no weekend)
Five 2's = 5x7,5 = 37,5h
Six 3's, but 3 of them are on a weekend (P8, W8, AD8) so ill do = 3x7,5 = 22,5h
and 3x6,5 = 13
Together 22+37,5+22,5+19,5 = 101,5h

Row 10:
two 1's, but one of them is on a sunday (X10) so 5,5+6 = 11,5h
one 2 = 1x7,5 = 7,5h
three 3's, one of them on a sunday (AE10) so ill do 7,5+7,5+6,5 = 21,5h
seven PAIRS of 4, which 2 of them end on a weekend (W10, AD10). in that case ill do:
15,5+15,5+15,5+15,5+15,5+16+16 = 109,5h
Together: 11,5+7,5+21,5+109,5 = 150h oh looks like I made a mistake with writing the 149 in my sheet!

I hope these are a good enough explanation and example. I am sorry if it's hard to understand!