CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2013
Uzair Ahmed
Uzair Ahmed

Reputation: 13

How to Filter out data ranges when start and end dates are given?

I have been given a set of start dates and stop dates for working days, I have also been given the working days and the daily rate. A person works a certain amount of days between for example Jan 1st 2018 and Feb 10th 2018, now the user wants to generate a report for cost from Jan 15 to Feb 15, how would I find out the number of working days for this person.

I've tried using the date filters on Excel but when I filter for February then January disappears. And it doesn't work since the guy worked 15 days in January and 10 days in February, so the month filter is useless. I'm stuck.

enter image description here

Upvotes: 1

Views: 360

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Use SUMPRODUCT to iterate the days in from the start to the finish and find if the dates fall in the Month provided and are Monday - Friday then multiply that by the daily rate:

=SUMPRODUCT((TEXT(ROW(INDEX($XFD:$XFD,A2):INDEX($XFD:$XFD,B2)),"mmmm") =E5)*(WEEKDAY(ROW(INDEX($XFD:$XFD,A2):INDEX($XFD:$XFD,B2)),2)<6)*D2)

enter image description here

Upvotes: 1

SandPiper
SandPiper

Reputation: 2906

First, make sure that your data is set up in a table. Then, from the Autofilter dropdown arrow in the column header, go to Date Filters, then use the Between filter.

More on Autofilter can be found at this Microsoft support link: https://support.office.com/en-us/article/use-autofilter-to-filter-your-data-7d87d63e-ebd0-424b-8106-e2ab61133d92

Upvotes: 0

Related Questions