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

1
votes

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

2
excelvbaexcel-formulaexcel-2013
Please mock up some data and expected output. Then edit the question to include this mock up. The explanation is hard to understand without a visual representation. – Scott Craner
Ive added an image – Uzair Ahmed
How are we supposed to now which days belong to which month? Do we assume Monday - Friday only? – Scott Craner
Yes working day are considered from mon - friday – Uzair Ahmed

2 Answers

1
votes

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

0
votes

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