Annual Leaver Tracker

[samleighcattrall]

Hello,

I am trying to make an annual leave tracker for my partner, which displays the following things.

Total Annual Leave
Annual Leave Taken(Historic)
Annual Leave Taken(Future Dates)
Annual Leave Used.

The issue I am finding, is that because her annual leave is calculated in hours, like many people I am sure, however each day of the week has a different allocation of hours. This is due to the length of the shift allocated to those days. The allocation of hours are as follows.

Monday/Tuesday - 8 Hours per day
Wednesday-Sunday - 9 hours per day.

I intend to go with a simple, Start date and End date in columns A and B, and use column C to calculate the hours. However the calcuation would have to identify the days included and between columns A and B, based on the hours stated above.

Any help would be much appreciated.

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G
1				menu			holidays
2	Start Date	End Date					1/1/2022
3	5/1/2022	5/1/2022	9	1	8		1/15/2022
4	6/3/2022	6/4/2022	18	2	8		5/30/2022
5	9/1/2022	9/12/2022	96	3	9		7/4/2022
6	11/20/2022	11/30/2022	77	4	9		9/3/2022
7				5	9		11/11/2022
8				6	9		11/24/2022
9				7	9		11/25/2022
10							12/25/2022
Sheet3

Cell Formulas
Range	Formula
C3:C6	C3	=LET(a,(SEQUENCE(B3-A3+1,1,A3)),c,ISNA(MATCH(a,$G$2:$G$10,0)),d,FILTER(a,c),b,XLOOKUP(WEEKDAY(d,11),$D$3:$D$9,$E$3:$E$9,,0),SUM(b))

 

[samleighcattrall]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G
1				menu			holidays
2	Start Date	End Date					1/1/2022
3	5/1/2022	5/1/2022	9	1	8		1/15/2022
4	6/3/2022	6/4/2022	18	2	8		5/30/2022
5	9/1/2022	9/12/2022	96	3	9		7/4/2022
6	11/20/2022	11/30/2022	77	4	9		9/3/2022
7				5	9		11/11/2022
8				6	9		11/24/2022
9				7	9		11/25/2022
10							12/25/2022
Sheet3

Cell Formulas
Range	Formula
C3:C6	C3	=LET(a,(SEQUENCE(B3-A3+1,1,A3)),c,ISNA(MATCH(a,$G$2:$G$10,0)),d,F

Thank you so much for the speedy reply. This is exactly what I needed.

 

[samleighcattrall]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G
1				menu			holidays
2	Start Date	End Date					1/1/2022
3	5/1/2022	5/1/2022	9	1	8		1/15/2022
4	6/3/2022	6/4/2022	18	2	8		5/30/2022
5	9/1/2022	9/12/2022	96	3	9		7/4/2022
6	11/20/2022	11/30/2022	77	4	9		9/3/2022
7				5	9		11/11/2022
8				6	9		11/24/2022
9				7	9		11/25/2022
10							12/25/2022
Sheet3

Cell Formulas
Range	Formula
C3:C6	C3	=LET(a,(SEQUENCE(B3-A3+1,1,A3)),c,ISNA(MATCH(a,$G$2:$G$10,0)),d,FILTER(a,c),b,XLOOKUP(WEEKDAY(d,11),$D$3:$D$9,$E$3:$E$9,,0),SUM(b))

Only issue I have found, is when i've pulled this formula down to cover multiple cells in column C, if there is no data in columns in A/B it will populate "9" in the cells in column C. If you have a fix for this, that would be appreciated, otherwise I will drag the formula down when required. Thank you

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G	H	I
1				menu			holidays
2	Start Date	End Date					1/1/2022
3	5/1/2022	5/1/2022	9	1	8		1/15/2022		9
4	6/3/2022	6/4/2022	18	2	8		5/30/2022		18
5	9/1/2022	9/12/2022	96	3	9		7/4/2022		96
6	11/20/2022	11/30/2022	77	4	9		9/3/2022		77
7				5	9		11/11/2022
8				6	9		11/24/2022
9				7	9		11/25/2022
10							12/25/2022
Sheet3

Cell Formulas
Range	Formula
I3:I6	I3	=LET(a,(SEQUENCE(B3-A3+1,1,A3)),c,ISNA(MATCH(a,$G$2:$G$10,0)),d,FILTER(a,c),b,XLOOKUP(WEEKDAY(d,11),$D$3:$D$9,$E$3:$E$9,,0),SUM(b))
C3:C10	C3	=IF(A3<>"",LET(a,(SEQUENCE(B3-A3+1,1,A3)),c,ISNA(MATCH(a,$G$2:$G$10,0)),d,FILTER(a,c),b,XLOOKUP(WEEKDAY(d,11),$D$3:$D$9,$E$3:$E$9,,0),SUM(b)),"")