Formulas to help me show half days over 4 weeks

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

ABCDEFGHIJK
Week 1 daysWeek 2 DaysWeek 3 DaysWeek 4 DaysHalf Day StartHalf day EndDays WK1Days WK2Days WK3Days WK4
553yesyes4.552.50
03yesyes0200
11noyes1.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
 

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
Joined
Jul 30, 2020
Messages
146
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
@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
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
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))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top