Referencing accrued leave value for last last months last working day

[ConstantCustard]

I have changed contract and the new leave policy is only for working days. Being in the middle east, the weekend is Friday and Saturday, which is why I have used WORKDAY.INTL.

The issue I have is transferring the accrued leave balance from the last working day of the previous month to the starting balance for the next month.
As this is working days, the last working day will change cell reference for each month and year.

I can reference the total accrued leave from the previous month manually as I setup the calendar for each year, however I am hoping there is a smarter way to achieve this

For January the last working day accrued leave is at Y6
I am trying to reference this value in B9 to act as the starting balance for February
As you can see for each month the cell reference for the last working day accrued leave changes.
This is where my knowledge of Excel runs out.

July 19 trial Leave planner.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
1	Year	2023
2	0.1576
3	H		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
4	0	1	Sun 01 Jan	Mon 02	Tue 03	Wed 04	Thu 05	Sun 08	Mon 09	Tue 10	Wed 11	Thu 12	Sun 15	Mon 16	Tue 17	Wed 18	Thu 19	Sun 22	Mon 23	Tue 24	Wed 25	Thu 26	Sun 29	Mon 30	Tue 31	Wed 01
5			H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
6			0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
7		2	Wed 01 Feb	Thu 02	Sun 05	Mon 06	Tue 07	Wed 08	Thu 09	Sun 12	Mon 13	Tue 14	Wed 15	Thu 16	Sun 19	Mon 20	Tue 21	Wed 22	Thu 23	Sun 26	Mon 27	Tue 28	Wed 01	Thu 02	Sun 05	Mon 06
8			H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
9		0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
10		3	Wed 01 Mar	Thu 02	Sun 05	Mon 06	Tue 07	Wed 08	Thu 09	Sun 12	Mon 13	Tue 14	Wed 15	Thu 16	Sun 19	Mon 20	Tue 21	Wed 22	Thu 23	Sun 26	Mon 27	Tue 28	Wed 29	Thu 30	Sun 02	Mon 03
11			H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
12		0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
2023

Cell Formulas
Range	Formula
C4,C10,C7	C4	=DATE($B$1,$B4,1)
D4,D10:Z10,D7	D4	=WORKDAY.INTL($C4,C$3,"0000110")
E4:Z4	E4	=WORKDAY.INTL($C4,D3,"0000110")
C6	C6	=SUM((IF(C5<>$A$3,$A$2)),(IF(C34=$A$3,$A$4)))
D6:Y6,D12:X12,D9:Y9	D6	=SUM((IF(D5<>$A$3,$A$2)),(IF(D5=$A$3,$A$4)),C6)
E7:Z7	E7	=WORKDAY.INTL($C7,D3,"0000110")
B9	B9	=Y6
C9,C12	C9	=SUM((IF(C8<>$A$3,$A$2)),(IF(C38=$A$3,$A$4)),B9)
B12	B12	=V9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U4:Z4	Expression	=MONTH(U4)<>$B$4	text	NO
U7:Z7	Expression	=MONTH(U7)<>$B$7	text	NO
U10:Z10	Expression	=MONTH(U10)<>$B$10	text	NO
C4:Z39	Dates Occurring	today	text	NO
C6:Z6,B9:Z9,B12:Z12,B15:Z15,B18:Z18,B21:Z21,B24:Z24,B27:Z27,B30:Z30,B33:Z33,B36:Z36,B39:Z39	Cell Value	>=10	text	NO
C6:Z6,B9:Z9,B12:Z12,B15:Z15,B18:Z18,B21:Z21,B24:Z24,B27:Z27,B30:Z30,B33:Z33,B36:Z36,B39:Z39	Cell Value	between 0 and 1	text	NO

 

[OaklandJim]

Ranges B15 - Z39 are not included. What is in those cells? They seem important? What are the values in cells A2, A3, and A4?

 

[OaklandJim]

If I understand the need try this formula in cell B9: =MAX( OFFSET(B9, -3, 1, 1, 24) ) to get accrued value for January. Likewise, Copy to B12 to get the value from February.

How do you specify the accrued value from the previous year?

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this the sort of thing you are after? I have also suggested alternatives to some of your other formulas which may simplify and/or make more uniform the formula structures in your worksheet.

23 07 24.xlsm
	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
1	Year	2023
2	0.1576
3	H		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
4	0	1	Sun 01 Jan	Mon 02	Tue 03	Wed 04	Thu 05	Sun 08	Mon 09	Tue 10	Wed 11	Thu 12	Sun 15	Mon 16	Tue 17	Wed 18	Thu 19	Sun 22	Mon 23	Tue 24	Wed 25	Thu 26	Sun 29	Mon 30	Tue 31
5			H	H	L	H	H	H	X	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
6			0	0	0.1576	0.1576	0.1576	0.1576	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152	0.3152
7		2	Wed 01 Feb	Thu 02	Sun 05	Mon 06	Tue 07	Wed 08	Thu 09	Sun 12	Mon 13	Tue 14	Wed 15	Thu 16	Sun 19	Mon 20	Tue 21	Wed 22	Thu 23	Sun 26	Mon 27	Tue 28
8			H	H	H	x	x	x	x	x	x	x	x	x	x	x	x	x	H	H	H	H
9		0.3152	0.3152	0.3152	0.3152	0.4728	0.6304	0.788	0.9456	1.1032	1.2608	1.4184	1.576	1.7336	1.8912	2.0488	2.2064	2.364	2.364	2.364	2.364	2.364
10		3	Wed 01 Mar	Thu 02	Sun 05	Mon 06	Tue 07	Wed 08	Thu 09	Sun 12	Mon 13	Tue 14	Wed 15	Thu 16	Sun 19	Mon 20	Tue 21	Wed 22	Thu 23	Sun 26	Mon 27	Tue 28	Wed 29	Thu 30
11			H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
12		2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364	2.364
Leave

Cell Formulas
Range	Formula
C4,C10,C7	C4	=DATE(B$1,B4,1)
D4:Z4,D10:Z10,D7:Z7	D4	=LET(d,IFERROR(WORKDAY.INTL(C4,1,"0000110"),0),IF(DAY(d)<C$3,"",d))
C6:Z6,C12:Z12,C9:Z9	C6	=IF(C4="","",B6+IF(C5=$A$3,$A$4,$A$2))
B9,B12	B9	=LOOKUP(9^9,C6:Z6)

 

[ConstantCustard]

If I understand the need try this formula in cell B9: =MAX( OFFSET(B9, -3, 1, 1, 24) ) to get accrued value for January. Likewise, Copy to B12 to get the value from February.

How do you specify the accrued value from the previous year?

Thanks, your formula has done the trick. Last year's balance would be manually placed in B6

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.