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

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Saba Sabaratnam

##### Active Member
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. ?‍

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,528
Messages
5,854,278
Members
431,633
Latest member
Smugpop

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

### Which adblocker are you using?

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

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