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

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
Joined
May 26, 2018
Messages
196
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. 🤦‍♀️
 

Watch MrExcel Video

Forum statistics

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

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