Excel - have two date columns Start dt and Finish dt how to filter data in a between a date range?

Question

Excel - have two date columns Start dt and Finish dt how to filter data in a between a date range?

Lorna C 20

I need to help w/Excel. Trying to figure out how to filter data that has two date columns where the filter is a date range. my worksheet - see example below - has the following columns with TWO date type fields: Start DateFinish DateHow do I create a Filter or something to display only data that falls between the two dates: ex I want to filter/display only data rows that fall within a date range of: 9/1/2025 to 11/30/2025.

I need only data rows returned if the search criteria falls in between the 'Start Date' & 'Finish Date'

this will show management and business team members which Tasks (Task Names) are valid / being worked during the months of Sept, Oct and Nov.

example 'Task Name' 'Test Env window' should return in the filtered data set because the search criteria dates fall between it's Start and Finish dates. Hoping someone can help....

(the Search Criteria fields listed at top right have no formulas other than the two date entry fields have been formatted as 'date', they are just fields that I'm wanting to use for this filter formula.)

User's image

Barry Schwarz 5,756 Reputation points

2025-09-26T20:16:56.59+00:00

Do require that the entire effort fall within the two dates? Or is it sufficient that any part of the effort falls within the window?

Your stated goal is to show management what tasks are being worked in September to November. "MapTest Env window" started in August but was worked for 30 days in September. Do you want to show that or not?
Lorna C 20 Reputation points

2025-09-26T21:57:52.31+00:00

Hi, Thank you for your reply. Need each TASKs effort, not entire project. so need each Task row's individual %'s.
Lorna C 20 Reputation points

2025-09-26T22:01:22.4366667+00:00

I have already added a 'Current date' cell at the top left of the table. =TODAY()

Answer accepted by question author

0 additional answers

Your answer

Barry Schwarz 5,756 Reputation points

2025-09-26T20:16:56.59+00:00

Do require that the entire effort fall within the two dates? Or is it sufficient that any part of the effort falls within the window?

Your stated goal is to show management what tasks are being worked in September to November. "MapTest Env window" started in August but was worked for 30 days in September. Do you want to show that or not?
Lorna C 20 Reputation points

2025-09-26T21:57:52.31+00:00

Hi, Thank you for your reply. Need each TASKs effort, not entire project. so need each Task row's individual %'s.
Lorna C 20 Reputation points

2025-09-26T22:01:22.4366667+00:00

I have already added a 'Current date' cell at the top left of the table. =TODAY()

Answer 1

Bernie Deitrick 490

The logic is: the end filter date is >= project start date AND the start filter date is <= project end date.

To apply that, use a column of formulas, like

=AND($J$4>=I6,$I$4<=J6)

Where I4 is the start date, J4 is the end date, and row 6 ahs the start and end dates of your projects.

Then filter on TRUE for that column to show projects that overlap those dates.

Lorna C 20 Reputation points

2025-09-26T21:02:36.5866667+00:00

Thank you this worked out perfectly. I really appreciate your help!! Wondering if would you happen to know a formula that I could use for an added column called '% Complete'. Where you take into account current date and Start Date and Finish Dates {project active dates) to get the % of completion?
Barry Schwarz 5,756 Reputation points

2025-09-27T07:50:40.3933333+00:00

If you are willing to assume linear progress based solely on days, then you could use an expression of the form MIN(100,(today-start)/(end-start)). If you need to consider only workdays, then use the NETWORKDAYS function instead of number of days for both numerator and denominator.

To handle tasks that have not started yet, either imbed the formula in an IF expression or use MAX(0,MIN(...)).

Share via

Excel - have two date columns Start dt and Finish dt how to filter data in a between a date range?

0 additional answers

Your answer