Hello all, sorry to bother you all. I am struggling here.
Scenario;
Column A (Created) in Data sheet contains 3000+ dates ranging from 01/01/2018 to 30/06/2018, there could be several instances of the same dates.
Column B (Closed) in Data sheet contains 3000+ dates ranging from 01/01/2018 to 30/06/2018, there could be several instances of the same dates.
---
The dates are formatted and cleaned using INT for both colums so the dates present themselves as "DD/MM/YYYY" no serial stuff going on.
---
As an Example March has 666 records created between the dates of 01/01/2018 and 31/01/2018 of which 573 of those were closed in the same month leaving an surplus of 93 records that were left open as of the 01/02/2018, this is calculating it manually just using filters and im trying to return that as a formula.
Formula I am using to the the 666 figure;
=COUNTIFS(Data!$A:$A,">="&$A6,Data!$A:$A,"<="&$B6)
A6 is 01/01/2018
B6 is 31/01/2018
Now what I cannot figure out is the surplus formula, the above formula looks in column A and counts all records where the date in A is between the 1st and 31st. The next formula needs to look at this formula to validate but then also apply its own formula to say;
Count all records in column B where the date in B is between the 1st and 31st but also where the date in A is between the 1st and 31st.
Thankyou.
Scenario;
Column A (Created) in Data sheet contains 3000+ dates ranging from 01/01/2018 to 30/06/2018, there could be several instances of the same dates.
Column B (Closed) in Data sheet contains 3000+ dates ranging from 01/01/2018 to 30/06/2018, there could be several instances of the same dates.
---
The dates are formatted and cleaned using INT for both colums so the dates present themselves as "DD/MM/YYYY" no serial stuff going on.
---
As an Example March has 666 records created between the dates of 01/01/2018 and 31/01/2018 of which 573 of those were closed in the same month leaving an surplus of 93 records that were left open as of the 01/02/2018, this is calculating it manually just using filters and im trying to return that as a formula.
Formula I am using to the the 666 figure;
=COUNTIFS(Data!$A:$A,">="&$A6,Data!$A:$A,"<="&$B6)
A6 is 01/01/2018
B6 is 31/01/2018
Now what I cannot figure out is the surplus formula, the above formula looks in column A and counts all records where the date in A is between the 1st and 31st. The next formula needs to look at this formula to validate but then also apply its own formula to say;
Count all records in column B where the date in B is between the 1st and 31st but also where the date in A is between the 1st and 31st.
Thankyou.
