Date Check Array

[ejoneslor]

I need to write an array to help me check the length of outstanding actions.

I have some simulated data in my attached spreadsheet. Items in column A, opened date in column B and closed date in column C. I need to count, for the data without a closed date how long have they been open, ie compare the opened date to todays date (cell B1).

I've mocked up a table (highlighted) of what I expect the result should look like if we count how many are less that 30 days, 30-60 days or over 60 days.

I struggle with arrays, can anyone help?

Any and all help appreciated.

Ed

Day Count Example.xlsx

[samba_ravi]

f6=COUNTIFS(B4:B23,">"&TODAY()-30,C4:C23,"")

=COUNTIFS(B4:B23,">"&TODAY()-30,C4:C23,"")

g6=COUNTIFS(B4:B23,">="&TODAY()-60,B4:B23,"<="&TODAY()-30,C4:C23,"")

=COUNTIFS(B4:B23,">="&TODAY()-60,B4:B23,"<="&TODAY()-30,C4:C23,"")

h6=COUNTIFS(B4:B23,"<"&TODAY()-60,C4:C23,"")

=COUNTIFS(B4:B23,"<"&TODAY()-60,C4:C23,"")

[ejoneslor]

Siva,

Thanks for that. So it looks like I was making it too complicated by pointing to a cell with todays date in instead of just writing the reference in the formula. A much simpler solution.

Thanks,

Ed

[ejoneslor]

Siva,

Is there a way of doing the same count for those items that have been closed? ie how many were closed within 30 days, between 30 and 60 etc....

Ed