Column A contains cells of different dates. I want to:
use conditional format to change the background color of the cell to red when it is at least 1 years old
have a cell that contains the number of cells in Column A whose dates are less than 24 months old
The former seems straightforward, but how would one implement the latter?
=SUMPRODUCT(A:A>EDATE(TODAY();-24))
@Mike Kaganski Thanks. How can the function count only values that are dates? For some of the cells, they have text like "denied" that is currently counted with the function.
On a somewhat unrelated note, for the following data:
Person A | P | 1/3/16
Person A | P | denied
Person A | B | 1/3/19
Person A | P | 1/3/20
I want to count number of dates within 24 months only for those those entries
with "P". Is the canonical/best and/or most efficient way to do the following:
=SUMPRODUCT(C1:C4>EDATE(TODAY(),-24))-SUMPRODUCT(B1:B4="B")? Is there an
alternative I might want to consider in common situations?
=SUMPRODUCT(ISNUMBER(A:A);A:A>EDATE(TODAY();-24);B:B="B")
Asked: 2020-03-20 08:45:52 +0100
Seen: 93 times
Last updated: Mar 20 '20
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.