# Formulas to help me show half days over 4 weeks

#### tonywatsonhelp

##### Well-known Member
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,

thanks

Tony

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Habtest

##### Board Regular
Hi Tony, hope it works.

Book1
ABCDEFGHIJKLMN
1ABCDEFGHIJK
2Week 1 daysWeek 2 DaysWeek 3 DaysWeek 4 DaysHalf Day StartHalf day EndDays WK1Days WK2Days WK3Days WK4
3553yesyes4.552.50
403yesyes0200
511noyes10.500
6
7Test
85550noyes554.5013
90320yesyes02.51.5023
100230yesno01.53023
Sheet1
Cell Formulas
RangeFormula
H8:H10,H3:H5H3=MAX(A3-IF(\$E3="yes",0.5)-IF(AND(B3=0,\$F3="yes"),0.5),0)
I8:J10,I3:J5I3=MAX(B3-IF(AND(H3=0,\$E3="yes"),0.5)-IF(AND(C3=0,\$F3="yes"),0.5),0)
K8:K10,K3:K5K3=MAX(D3-IF(\$F3="yes",0.5),0)
A8:A10A8=IF(COLUMN()=M8,RANDBETWEEN(1,5),0)
B8:B10B8=IF(OR(A8=0,COLUMN()=N8),RANDBETWEEN(1,5),5)
C8:C10C8=IF(COLUMN()>N8,0,IF(COLUMN()=N8,RANDBETWEEN(1,5),5))
D8:D10D8=IF(COLUMN()=N8,RANDBETWEEN(1,5),0)
E8:F10E8=IF(RANDBETWEEN(0,1)=0,"yes","no")
M8:M10M8=RANDBETWEEN(1,2)
N8:N10N8=RANDBETWEEN(M8,4)

#### Snakehips

##### Well-known Member
Hi Tony,
Looks like I need to update my xl2b so no upload of my sample.

However, maybe try the below formula in H3

VBA Code:
``=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

##### Well-known Member
Hi Snakehips, Thanks very much this looks great,
Habtest, thanks very much this will come in handy as well.
Thanks
Tony

#### Snakehips

##### Well-known Member
@tonywatsonhelp Sorry, some how managed to omit a \$ from the above formula.

Should be.......
VBA Code:
``=IF(A3<1,0,A3-IF(A3=SUM(\$A3:A3),(\$E3="yes")/2,0)-IF(A3=SUM(A3:\$D3),(\$F3="yes")/2,0))``

#### Snakehips

##### Well-known Member
XL2BB sorted so for clarity....

MrExcelFeb2021.xlsx
ABCDEFGHIJK
2Week 1 daysWeek 2 DaysWeek 3 DaysWeek 4 DaysHalf Day StartHalf day EndDays WK1Days WK2Days WK3Days WK4
3553Yesyes4.552.50
403yesno02.500
511noyes10.500
60732YesNo06.532
Sheet3
Cell Formulas
RangeFormula
H3:K6H3=IF(A3<1,0,A3-IF(A3=SUM(\$A3:A3),(\$E3="yes")/2,0)-IF(A3=SUM(A3:\$D3),(\$F3="yes")/2,0))

Replies
3
Views
585
Replies
1
Views
141
Replies
8
Views
135
Replies
1
Views
121
Replies
3
Views
247

1,129,822
Messages
5,638,534
Members
417,033
Latest member
JKThai

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back