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

tatarrific

New Member
Joined
Feb 26, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

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

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
404
Office Version
  1. 365
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
Joined
Feb 26, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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. ?‍♀️
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

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
Top