creating an excel formula that counts the number of entries that are outdated.

[amyexcel]

how would I create an excel formula that counts the number of entries that are outdated.
I have a column (B) with Months entered (mm-yy) and i need to have a running count of how many have surpassed the 6 month mark.
Really no clue of how to create a formula to address this. #wishiwenttoexcelschool

 

[Logit]

Paste this formula in any cell that is not in Column B :

=COUNTIF(B:B,"<" & DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()+1)))

 

[etaf]

are the dates real dates OR Text ?
change the format to General and the dates should change from a format of mm-yy to a number
otherwise if text maybe more difficult to work out

if real dates then we can use a countif ()
=COUNTIF(B2:B50,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(1)))
See D2 below
this is from the 1st of the month based on todays date - to 6mth ago

Time to 6mins ETAF.xlsx
	A	B	C	D	E
1
2	1	02-20		8
3	2	03-20
4	3	04-20
5	4	05-20
6	5	05-20
7	6	06-20
8	7	07-20
9	8	08-20
10	9	09-20	9/1/20
11	10	10-20
12	11	11-20
13	12	12-20
14	13	01-21
15	14	02-21
Sheet2

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(B2:B50,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(1)))
C10	C10	=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(1))

 

[jtakw]

Hi,

Assuming Real Date values in Column B.
D2 will Count how many Dates are 6 months or older, to the Day of Today.
E2 will Count how many Dates are More than 6 months old, to the Day of Today.

Book3.xlsx
	A	B	C	D	E
1
2	1	2/1/2020		10	9
3	2	3/2/2020
4	3	4/1/2020
5	4	5/1/2020
6	5	5/31/2020
7	6	6/30/2020
8	7	7/30/2020
9	8	8/29/2020
10	9	9/28/2020
11	10	9/30/2020
12	11	11/27/2020
13	12	12/27/2020
14	13	1/26/2021
15	14	2/25/2021
16
Sheet882

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(B2:B50,"<="&EDATE(TODAY(),-6))
E2	E2	=COUNTIF(B2:B50,"<"&EDATE(TODAY(),-6))

 

[amyexcel]

Hi there, thank you so much. That has solved my query and allowed me to plough on! i've saved the formula for future reference. Really wish I was a pro at this excel stuff!

 

[etaf]

you are welcome

 

[jtakw]

You're welcome, thanks for the feedback.