Conditional date subtractions through an array

jwlanca

New Member
Joined
Jul 17, 2012
Messages
11
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).
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,148,363
Messages
5,746,268
Members
424,002
Latest member
anon341

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top