Finding rows where dates are within specified date range

[Heini]

Hi,
I have a data table where there are start and end date specified for each row. I would need to find all those rows where there is any number of days within year 2020 between the start date and end date. My table looks like this:

StartEndDate_table_exaple.jpg

Basically, the only record I'm not interested in is on row 5. I need support in figuring out the best formula to achieve this. Any ideas?

Br, Heini

[ORoos]

Hi Heini,

Try this in D2: =IF(OR(YEAR(B2)=2020,YEAR(C2)=2020),"in 2020","No days in 2020")

[arthurbr]

Hi
and when found what should happen? Highlight them, extract them?
Have you tried a filter ?

[Heini]

Thanks ORoos for a quick reply.
With the suggested formula =IF(OR(YEAR(B2)=2020,YEAR(C2)=2020),"in 2020","No days in 2020") it does not unfortunately catch row 8 where actually the entire year 2020 is within the range of start and end date.

[arthurbr]

Then try

Please Login or Register  to view this content.

Adding a small sample sheet instead of a picture helps ( see yellow banner)

[Heini]

Hi
and when found what should happen? Highlight them, extract them?
Have you tried a filter ?

Once I have found all the rows where there is a touchpoint to year 2020, I will extract them for further processing.

[Heini]

Then try

Please Login or Register  to view this content.

Adding a small sample sheet instead of a picture helps ( see yellow banner)

Thank you arthurbr! This formula did the trick!

[arthurbr]

You're welcome