Ask Your Question
0

Count number of cells whose date is less than 24 months old?

asked 2020-03-20 08:45:52 +0100

zf gravatar image

updated 2020-08-09 20:24:31 +0100

Alex Kemp gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-03-20 09:30:16 +0100

=SUMPRODUCT(A:A>EDATE(TODAY();-24))

edit flag offensive delete link more

Comments

@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?

zf gravatar imagezf ( 2020-03-25 21:51:28 +0100 )edit

=SUMPRODUCT(ISNUMBER(A:A);A:A>EDATE(TODAY();-24);B:B="B")

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-25 22:04:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-20 08:45:52 +0100

Seen: 93 times

Last updated: Mar 20 '20