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

#### tatarrific

##### New Member
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
ABCDEFGHIJKLMNOPQRST
1Fiscal Year Start Date1/1/2021
2Fiscal Year End Date12/31/2021
3COLA1.50%222222222222
4Name Start DateEnd DateAnnual SalaryCOLASalary AdjustmentTotal SalaryJanuary-21February-21March-21April-21May-21June-21July-21August-21September-21October-21November-21December-21TOTAL
5Gilbert Frey 1/10/202012/31/2021\$50,000\$750\$50,7504,229.174,229.174,229.174,229.174,229.174,229.174,229.174,229.174,229.174,229.174,229.174,229.1750,750.00
6Aaliyah Henry 5/1/202112/31/2021\$50,000\$750\$50,750----4,229.174,229.174,229.174,229.174,229.174,229.174,229.174,229.1733,833.33
7Enrique Spence 1/16/202012/31/2021\$51,250\$769\$2,500\$54,5194,543.234,543.234,543.234,543.234,543.234,543.234,543.234,543.234,543.234,543.234,543.234,543.2354,518.75
8Cole Byers 7/28/202012/31/2021\$52,400\$786\$53,1864,432.174,432.174,432.174,432.174,432.174,432.174,432.174,432.174,432.174,432.174,432.174,432.1753,186.00
9Sidney Fischer 12/30/202012/31/2021\$56,838\$853\$57,6914,807.554,807.554,807.554,807.554,807.554,807.554,807.554,807.554,807.554,807.554,807.554,807.5557,690.57
10Keiron Portillo 1/13/201912/31/2021\$58,000\$870\$58,8704,905.834,905.834,905.834,905.834,905.834,905.834,905.834,905.834,905.834,905.834,905.834,905.8358,870.00
11Ruby Parrish 6/23/20199/30/2021\$60,000\$900\$60,9005,075.005,075.005,075.005,075.005,075.005,075.005,075.005,075.005,075.00---45,675.00
12Alana Delacruz 8/15/201912/31/2021\$65,000\$975\$65,9755,497.925,497.925,497.925,497.925,497.925,497.925,497.925,497.925,497.925,497.925,497.925,497.9265,975.00
13Liliana Adams 5/12/202012/31/2021\$68,800\$1,032\$69,8325,819.335,819.335,819.335,819.335,819.335,819.335,819.335,819.335,819.335,819.335,819.335,819.3369,832.00
14Dan Brewer 11/5/202012/31/2021\$70,000\$1,050\$3,500\$74,5506,212.506,212.506,212.506,212.506,212.506,212.506,212.506,212.506,212.506,212.506,212.506,212.5074,550.00
15582,288.008,734.326,000.00597,022.3245,522.6945,522.6945,522.6945,522.6949,751.8649,751.8649,751.8649,751.8649,751.8644,676.8644,676.8644,676.86564,880.65
Sheet1
Cell Formulas
RangeFormula
G5:G14G5=SUM(D5:F5)
H5:S14H5=IF(AND(\$B5<=H\$4,\$C5>=H\$4),(\$G5/24)*H\$3,0)
T5:T14T5=SUM(H5:S5)
C12:C14,C5:C10C5=+\$B\$2
E5:E14E5=+D5*\$B\$3
D15:T15D15=SUM(D5:D14)

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

Last edited by a moderator:

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Saba Sabaratnam

##### Board Regular
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

##### New Member
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.

Replies
3
Views
86
Replies
1
Views
72
Replies
12
Views
331
Replies
5
Views
116
Replies
7
Views
2K

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back