Calendar Project

[Tigerexcel]

Working on a Planning project for Staff Leave. The first extract shows the current situation, the second is the desired output.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	First Name	Leave Type	Quantity - Days	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jack	Long Service Leave	65	22/06/2021	18/09/2021
3	Mary	Annual Leave	18	12/10/2021	20/11/2021
4	Mary	Annual Leave	2	21/11/2021	22/11/2021
5	Peter	Long Service Leave	5	04/01/2021	08/01/2021
6
7
8	First Name	Leave Type	Quantity - Days	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	Jack	Long Service Leave	65	22/06/2021	18/09/2021						16	16	16	16
10	Mary	Annual Leave	20	12/10/2021	20/11/2021										9	11
11	Peter	Long Service Leave	5	04/01/2021	08/01/2021	5
Sheet1

The 3rd column records the number of days which need to be spread out across the next year, in some cases it's half leave but it's the figure contained in Column 3 that is important. The figure placed in the desired extract is simply the entry in Column 3 divided by the number of months spanned by Columns D & E. The first record is 65 divided by 4 and rounded up. Conditional Formatting is then applied to the relevant month(s). Would prefer a Function/Formula approach in the first instance but ultimately I'll take any approach that gets the job done.

 

[AlanY]

try this, Col R cater for the roundoff errors

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	First Name	Leave Type	Quantity - Days	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Adjustment
2	Jack	Long Service Leave	65	22/06/2021	18/09/2021						16	16	16	16				-1
3	Mary	Annual Leave	20	12/10/2021	20/11/2021										10	10		0
4	Peter	Long Service Leave	5	04/01/2021	08/01/2021	5												0
Sheet1

Cell Formulas
Range	Formula
F2:Q4	F2	=IF(AND(MONTH(DATEVALUE(F$1&"1"))>=MONTH($D2),MONTH(DATEVALUE(F$1&"1"))<=MONTH($E2)),ROUND($C2/(MONTH($E2)-MONTH($D2)+1),0),"")
R2:R4	R2	=SUM(F2:Q2)-C2

 

[AlanY]

this version taken into account of the days pro-rata within leave months

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	First Name	Leave Type	Quantity - Days	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jack	Long Service Leave	65	22/06/2021	18/09/2021						7	22	22	14
3	Mary	Annual Leave	20	12/10/2021	20/11/2021										10	10
4	Peter	Long Service Leave	5	04/01/2021	08/01/2021	5
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(ROUND(IF(MONTH(F$1&" 1")=MONTH($D2),MIN($C2,(DATE(YEAR($E2),MONTH(F$1&" 1"),EOMONTH(MONTH(F$1&" 1"),0)-1)-$D2+1)*$C2/($E2-$D2+1)),IF(MONTH(F$1&" 1")=MONTH($E2),$C2-SUM(E2:$F2),IF(AND(MONTH(F$1&" 1")>MONTH($D2),MONTH(F$1&" 1")<MONTH($E2)),DAY(DATE(YEAR($E2),MONTH(F$1&" 1"),EOMONTH(MONTH(F$1&" 1"),0)-1))*$C2/($E2-$D2+1),""))),0),"")
G2:Q4	G2	=IFERROR(ROUND(IF(MONTH(G$1&" 1")=MONTH($D2),MIN($C2,(DATE(YEAR($E2),MONTH(G$1&" 1"),EOMONTH(MONTH(G$1&" 1"),0)-1)-$D2+1)*$C2/($E2-$D2+1)),IF(MONTH(G$1&" 1")=MONTH($E2),$C2-SUM($F2:F2),IF(AND(MONTH(G$1&" 1")>MONTH($D2),MONTH(G$1&" 1")<MONTH($E2)),DAY(DATE(YEAR($E2),MONTH(G$1&" 1"),EOMONTH(MONTH(G$1&" 1"),0)-1))*$C2/($E2-$D2+1),""))),0),"")