Need to figure out a way to pull data based on a dynamic date range

[tatarrific]

Hi all - total newbie, need help please.

I was able to figure out how to create formulas to calculate prorated salary per month based on start/end dates, but am stumped on the next step. I want to be able to filter the totals for date ranges, so that I can say, show me salary totals from 1/1-6/30 only, or from 9/30-12/31 only. I thought there'd be an easy way to do it through a pivot, but maybe there's a better way to do it with a formula? Any help most appreciated.

Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Fiscal Year Start Date	1/1/2021
2	Fiscal Year End Date	12/31/2021
3	COLA	1.50%						2	2	2	2	2	2	2	2	2	2	2	2
4	Name	Start Date	End Date	Annual Salary	COLA	Salary Adjustment	Total Salary	January-21	February-21	March-21	April-21	May-21	June-21	July-21	August-21	September-21	October-21	November-21	December-21	TOTAL
5	Gilbert Frey	1/10/2020	12/31/2021	$50,000	$750		$50,750	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	50,750.00
6	Aaliyah Henry	5/1/2021	12/31/2021	$50,000	$750		$50,750	-	-	-	-	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	4,229.17	33,833.33
7	Enrique Spence	1/16/2020	12/31/2021	$51,250	$769	$2,500	$54,519	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	4,543.23	54,518.75
8	Cole Byers	7/28/2020	12/31/2021	$52,400	$786		$53,186	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	4,432.17	53,186.00
9	Sidney Fischer	12/30/2020	12/31/2021	$56,838	$853		$57,691	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	4,807.55	57,690.57
10	Keiron Portillo	1/13/2019	12/31/2021	$58,000	$870		$58,870	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	4,905.83	58,870.00
11	Ruby Parrish	6/23/2019	9/30/2021	$60,000	$900		$60,900	5,075.00	5,075.00	5,075.00	5,075.00	5,075.00	5,075.00	5,075.00	5,075.00	5,075.00	-	-	-	45,675.00
12	Alana Delacruz	8/15/2019	12/31/2021	$65,000	$975		$65,975	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	5,497.92	65,975.00
13	Liliana Adams	5/12/2020	12/31/2021	$68,800	$1,032		$69,832	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	5,819.33	69,832.00
14	Dan Brewer	11/5/2020	12/31/2021	$70,000	$1,050	$3,500	$74,550	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	6,212.50	74,550.00
15				582,288.00	8,734.32	6,000.00	597,022.32	45,522.69	45,522.69	45,522.69	45,522.69	49,751.86	49,751.86	49,751.86	49,751.86	49,751.86	44,676.86	44,676.86	44,676.86	564,880.65
Sheet1

Cell Formulas
Range	Formula
G5:G14	G5	=SUM(D5:F5)
H5:S14	H5	=IF(AND($B5<=H$4,$C5>=H$4),($G5/24)*H$3,0)
T5:T14	T5	=SUM(H5:S5)
C12:C14,C5:C10	C5	=+$B$2
E5:E14	E5	=+D5*$B$3
D15:T15	D15	=SUM(D5:D14)

Also asked here Need to figure out a way to pull data based on a dynamic date range

 

[Saba Sabaratnam]

The easiest way to do this is to use SUBTOTAL instead of SUM formula and use Filter to filter your Start and End date. SUM will total hidden columns while SUBTOTAL function ignores it.

Kind regards

Saba

 

[tatarrific]

Thank you, Saba! I don't know what I did with the setup of the spreadsheet, but I can't actually figure out how to filter on date - it pulls all the amounts under each month instead. ?‍