Copy rows from a table that are between 2 dates

Copper Contributor

I have a table that I would like to extract listed tasks in column A and the duration in column B if the start date is between the e12:ab12 dates. I would like to have the tasks ordered chronologically in column A and if there are multiple tasks starting the same date they should be ordered from shortest duration to longest. any help on this would be appreciated. Thanks in advance. 

 

FrogFace_0-1702614177264.png

 

FrogFace_1-1702614245487.png

 

6 Replies

Hi @FrogFace 
Try this approach using Filter and Sort functions to achieve that.
Here's the example formula:

=SORT(FILTER(A2:B100, (C2:C100>=E12)*(C2:C100<=AB12)), 1, 1, 2, 1)

A2:B100, is the range of tasks and durations
C2:C100, is the range start dates
E12 and AB12 are the start and end dates you want to filtered between.
Filter function filters the tasks based on the start date criteria
Sort function orders them chronologically and by duration if they have same start date.
*change/replace the ranges in the formula with your actual data ranges. Let me know if you need further assistance!

That doesn't seem to be a solution at all.
please assist

@FrogFace 
Could you provide a sample file.

@Rr_ 

 

I'm new to this community so it looks like im not allowed to upload documents without permission. When I enter your formula there is a spill error. another problem i can see happening with that formula is, i.e. the first given task may start before 12/11 (cell e12), say on 12/10, but with a duration of 3 days, that task should be included in the visual, but it looks like it would not be included using the formula provided above. Does that make sense? 

 

 

 

Thanks again for your help.

@FrogFace 
okay, try this adjusted formula

=SORT(FILTER(A2:B100, 
((C2:C100+E2:E100)>=E12)*(C2:C100<=AB12)), 1, 1, 2, 1)

The formula now checks if the end date of a task (start date + duration) falls within the desired date range. If a task starts before E12 but ends after it due to its duration, it will be included in the results.