Formulas to help me show half days over 4 weeks

[tonywatsonhelp]

Hi Everyone,
Ok so i'll create an example below as this is a bit hard to explain,
but i'm trying to work out holiday hours for our employees

so what i have is a 4 week holiday (no one can take more than 4 weeks at a time) but a lot of people start or end there holidays with a half day,
so heres my problems

in the grid below ive tried to show my setup and what i have

	A	B	C	D	E	F	G	H	I	J	K
	Week 1 days	Week 2 Days	Week 3 Days	Week 4 Days	Half Day Start	Half day End		Days WK1	Days WK2	Days WK3	Days WK4
	5	5	3		yes	yes		4.5	5	2.5	0
	0	3			yes	yes		0	2	0	0
	1	1			no	yes		1	.5

So heres the rules i need,
we have 4 weeks,
holiday can start in week 1 or week 2
and can end in week 1,2,3,4

so I need formulas that take into acount if there is a holiday and if there is a half day,
if so take any Half Start Day off the first week of day, and Half Day End off the last week

sound simple but i've been playing around with this for hours now,
please help if you can,

thanks

Tony

 

[Habtest]

Hi Tony, hope it works.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A	B	C	D	E	F	G	H	I	J	K
2	Week 1 days	Week 2 Days	Week 3 Days	Week 4 Days	Half Day Start	Half day End		Days WK1	Days WK2	Days WK3	Days WK4
3	5	5	3		yes	yes		4.5	5	2.5	0
4	0	3			yes	yes		0	2	0	0
5	1	1			no	yes		1	0.5	0	0
6
7	Test
8	5	5	5	0	no	yes		5	5	4.5	0		1	3
9	0	3	2	0	yes	yes		0	2.5	1.5	0		2	3
10	0	2	3	0	yes	no		0	1.5	3	0		2	3
Sheet1

Cell Formulas
Range	Formula
H8:H10,H3:H5	H3	=MAX(A3-IF($E3="yes",0.5)-IF(AND(B3=0,$F3="yes"),0.5),0)
I8:J10,I3:J5	I3	=MAX(B3-IF(AND(H3=0,$E3="yes"),0.5)-IF(AND(C3=0,$F3="yes"),0.5),0)
K8:K10,K3:K5	K3	=MAX(D3-IF($F3="yes",0.5),0)
A8:A10	A8	=IF(COLUMN()=M8,RANDBETWEEN(1,5),0)
B8:B10	B8	=IF(OR(A8=0,COLUMN()=N8),RANDBETWEEN(1,5),5)
C8:C10	C8	=IF(COLUMN()>N8,0,IF(COLUMN()=N8,RANDBETWEEN(1,5),5))
D8:D10	D8	=IF(COLUMN()=N8,RANDBETWEEN(1,5),0)
E8:F10	E8	=IF(RANDBETWEEN(0,1)=0,"yes","no")
M8:M10	M8	=RANDBETWEEN(1,2)
N8:N10	N8	=RANDBETWEEN(M8,4)

 

[Snakehips]

Hi Tony,
Looks like I need to update my xl2b so no upload of my sample.

However, maybe try the below formula in H3

=IF(A3<1,0,A3-IF(A3=SUM($A3:A3),(E3="yes")/2,0)-IF(A3=SUM(A3:$D3),($F3="yes")/2,0))

Drag formula down and across as required.

Hope that helps.

 

[tonywatsonhelp]

Hi Snakehips, Thanks very much this looks great,
Habtest, thanks very much this will come in handy as well.
Thanks
Tony

 

[Snakehips]

@tonywatsonhelp Sorry, some how managed to omit a $ from the above formula.

Should be.......

=IF(A3<1,0,A3-IF(A3=SUM($A3:A3),($E3="yes")/2,0)-IF(A3=SUM(A3:$D3),($F3="yes")/2,0))

 

[Snakehips]

XL2BB sorted so for clarity....

MrExcelFeb2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2	Week 1 days	Week 2 Days	Week 3 Days	Week 4 Days	Half Day Start	Half day End		Days WK1	Days WK2	Days WK3	Days WK4
3	5	5	3		Yes	yes		4.5	5	2.5	0
4	0	3			yes	no		0	2.5	0	0
5	1	1			no	yes		1	0.5	0	0
6	0	7	3	2	Yes	No		0	6.5	3	2
Sheet3

Cell Formulas
Range	Formula
H3:K6	H3	=IF(A3<1,0,A3-IF(A3=SUM($A3:A3),($E3="yes")/2,0)-IF(A3=SUM(A3:$D3),($F3="yes")/2,0))