Forum Discussion

FrogFace's avatar
FrogFace
Copper Contributor
Dec 15, 2023

Copy rows from a table that are between 2 dates

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 chronological...
Excel
Formulas and Functions
Rodrigo_'s avatar
Rodrigo_
Steel Contributor

FrogFace 
Could you provide a sample file.

FrogFace's avatar
FrogFace
Copper Contributor
Dec 16, 2023

Rodrigo_ 

 

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.

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Dec 17, 2023

    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. 

Resources