Help required in Filter formula

[GemBox]

Dear All,

I have requirement to find my tickets assigned and closed in between two dates using filter formula but it's not working as expected
I have applied this formula
=filter(Report!A2:H333,(Report!G2:G333>=Sheet1!C2)*(Report!H2:H333<=Sheet1!E2)) - it shows only assinged date records..
Report Column G contains assigned date
Report Column H contains closed date
Sheet1 column C2 is 'From' date
Sheet 1 column E2 is 'To' date

if i apply =filter(Report!A2:H333,(Report!G2:G333>=Sheet1!C2)+(Report!H2:H333<=Sheet1!E2)) - it shows complete records from array..

Am i doing wrong in the formula?

Regards
Gem

[Pepe Le Mokko]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[JohnTopley]

Please update your profile as appear to have Excel 365.

[GemBox]

Hello All,
Sample sheet attached..

[Glenn Kennedy]

It is not really stated what you expect to see as you rresult... but maybe this:

=FILTER(Data2!A3:F12,(Data2!E3:E12>=ExpectedResult!C2)*(Data2!F3:F12<=ExpectedResult!E2))

[GemBox]

Hello John,
I have already applied that formula but the results are wrong.. It's considering only Data2!F:F

Please find attached the sheet with expected result..

[JohnTopley]

Reply was from Glenn - I don't have OS 365.

[Glenn Kennedy]

1. I assume the double inclusion of 2 is a mistake.

2. Are you sure about 10? Closed a month before it was opened?

3. maybe:

=FILTER(Data2!A3:F12,((Data2!E3:E12>=ExpectedResult!C2)*(Data2!F3:F12<=ExpectedResult!E2)+(Data2!E3:E12>=ExpectedResult!C2)*(Data2!E3:E12<=ExpectedResult!E2)))