Conditional date subtractions through an array

[jwlanca]

I could use some help on a problem I've been struggling with!

A1 = 3/31/2020

B:B represents a range of dates (2018-2021) in which a support ticket was opened.
C:C represents a range of dates (2018-2021) in which that support ticket was closed.

• If the ticket is still open, C:C contains the value "Open" instead of a date

I want to find all cells in column B that are less than or equal to A1's date where column C is either greater than A1 or "Open"

Then I want to use that data to calculate how many days the average ticket was open as of A1's value (3/31/2020). So for each value in column B and C, B should be <= A1 and C should be > A1 or "Open", and the final value needs to find the average of the Open date compared to the value in A1 (in days).

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you also specify whether you are interested in formula or vba or power query solution?