Index/Match Formulas for Dates and business days

[banneduser123]

Hi,

if it helps, i've uploaded my file to dropbox:

Dropbox file


I have a workbook with two sheets. one called "2020", which is the main sheet and the other called "Data", where i'm basically just aggregating data.
the purpose of this file is to essentially keep track of my finances, factoring in my pay schedule and also my expenses.


the below range is what i'm currently trying to work on. i don't know how to populate the area while factoring in the dates, but i tried to arrange the data in the "Data" so it would be easy to create the formulas.
basically i'm looking to extract the data from the "Data" tab and populate each of the columns into the below range.
you'll see there are 3 sections - the top section with the headers, 2nd section called "Next Month" and a 3rd section where i would be able to input a time range and a sum of the expenses due during that period of time would be produced.

would greatly appreciate any help. and also happy to further provide more specific clarifications, as i was just trying to get out the basic idea

finl planner test.xlsx
	B	C	D	E	F	G	H	I	J	K
53	Due	Frequency	Type	Payment	Other Info.	Expense:	Prior	Actual	Difference	2021 Proj
54	Dec - 01	Annual	Home	From Checking	Policy Number	Property Taxes	500.00	5,000.00	4,986.39	5,199.46
55	Dec - 02	Monthly			Other Info text	Apple	50.00	50.00
56	Dec - 05	Monthly	Cable / Internet	Auto to Card		Insurance	436.00	436.00
57	Dec - 23	Monthly	Media	Auto to Card			131.00	131.00
58
59
60
61
62
63
64
65
66	Next Month
67	Jan - 01	Monthly	Media	Auto to Card		HBO	14.99	14.99
68	Dec - 02	Monthly			Other Info text	Apple	50.00	179.88
69	Dec - 05	Monthly	Cable / Internet	Auto to Card		Insurance	436.00	898.51
70	Dec - 23	Monthly	Media	Auto to Card			131.00	25.00
71
72
73
74
75
76
77	Time Range:	2/1/2021	7/18/2021	Expected Expenses:
78
79
80
81						Total Year	1,748.99	25,824.56
2020

Cell Formulas
Range	Formula
K54	K54	=(J54*4%)+I54
J54	J54	=SUM(I54:I80)-SUM(H54:H80)
J55:J57,J66:J67	J55	=I55-H55
H81	H81	=SUM(H54:H80)
I81	I81	=SUMIF(C54:C80,"Monthly",I54:I80)*12+SUMIF(C54:C80,"Bi-Annual",I54:I80)*2+SUMIF(C54:C80,"Annual",I54:I80)

this is the range from the Data tab:

finl planner test.xlsx
	L	M	N	O	P	Q	R	S
1	Month	Day	Frequency	Type	Payment	Other Info.	Amt.	Expenses
2	12	30	Annual	Home	From Checking	Other Info text	$ 5,000.00	Bill 1
3	4	15	Annual	Home			$ 500.00	Bill 2
4	xx	2	Monthly	Home		Other Info text	$ 50.00	Bill 3
5							Total	$ 600.00
6	8	1	Annual	Home	From Checking	Policy Number	$ 555.00	Bill 4
7	8	14	Annual	Car	Manual to Card	Member Number	$ 55.00	Bill 5
8	6	13	Bi-Annual	Car		Other Info text	$ 50.00	Bill 6
9	12	22					Total	$ 100.00
10	2	2	Annual	Retail	Auto to Card		$ 50.00	Bill 7
11	xx	5	Monthly	Cable / Internet	Auto to Card		$ 55.00	Bill 8
12	xx	23	Monthly	Media	Auto to Card		$ 5.00	Bill 9
13							Total	$ 60.00
14	9	3	Annual	Office			$ 555.00	Bill 10
15	12	19	Annual	Office / Retail / Media	Auto to Card		$ 55.00	Bill 11
16	xx	2	Monthly	Finances	Auto to Checking	Other Info text	$ 50.00	Bill 12
17							Total	$ 600.00
18	xx	9	Monthly	Home	Auto to Checking		$ 5.00	Bill 13
19							Total	$ 60.00
20	xx	1	Monthly	Savings	Auto to Checking		$ 50.00	Autosave to Savings
21							Total	$ 600.00
22	xx	6	Monthly	Media	Auto to Checking	Other Info text	$ 5.00	Bill 14
23							Total	$ 60.00
24	xx	6	Monthly	Media	Auto to Checking	Other Info text	$ 2.99	Bill 15
25							Total	$ 35.88
Data

Cell Formulas
Range	Formula
S5,S25,S23,S21,S19,S17,S13	S5	=R4*12
S9	S9	=R8*2