How to track expiry dates in a training spreadsheet

[DanielK2012]

Hi I am really struggling.

I have a training spreadsheet with circa 2000 dates in and i need to keep track of the following 3 things:

•Total valid certificates
•Total certificates within 1 month of expiry
•Total expired certificates

I already have conditional formatting to show which ones are expiring and color codes them accordingly but i need in the top of the spreadsheet a sort of table with the Count function which will count the above.

Is there any way i can do this.

For reference: Cells B8 to AA150 all contain dates.

I just need to know how to write the CountIF formulae (if that is the correct one) to take into account the month.

I though it would be something like
=countif(B8:AA150,<TODAY()) - For expired certificates
=countif(B8:AA150,>TODAY+Month? - For certificates expiring within the month
=countif(B8:AA150,>TODAY+2month? - for valid in date certificates?

PLEASE HELP

 

[WarPigl3t]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"]12/5/2014[/TD]
[TD="align: right"]5[/TD]
[TD]Total valid certificates[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]12/5/2015[/TD]
[TD="align: right"]5[/TD]
[TD]Total certificates within 1 month of expiry[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD="align: right"]4[/TD]
[TD]Total expired certificates[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]12/5/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]12/5/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]12/5/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]12/5/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]12/5/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]12/5/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"]12/5/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD="align: right"]12/5/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

B1 = COUNTIF($A$1:$A$14, ">" & TODAY() + "30")
B2 = COUNTIFS($A$1:$A$14,"<=" & TODAY()+ "30", $A$1:$A$14, ">=" & TODAY())
B3 = COUNTIF($A$1:$A$14, "<" & TODAY())

I assumed a month would be 30 days. Just replace the range $A$1:$A$14 with your range.

 

[Vidar]

Hi Daniel
For "Total valid certificates"
=COUNTIF($B$8:$AA$150,">="&TODAY())

Total certificates within 1 month of expiry
=COUNTIFS($B$8:$AA$150,"<"&EDATE(TODAY(),1),$B$8:$AA$150,">="&TODAY())

Total expired certificates
=COUNTIF($B$8:$AA$150,"<"&TODAY())

 

[DanielK2012]

Thank You Vidar, It was a great help

 

[DanielK2012]

Thank you WarPiglet, your table and formulas helped me immensely!
Thank you so much

 

[DanielK2012]

WarPiglet, Can you help further please?
I had conditional formatting in place but it all seems to have disappeared and there are no rules present on the spreadsheet.
Would you know how to apply these formulas to conditional formatting?

Please help if you can.

Thanks

 

[WarPigl3t]

Explain

 

[DanielK2012]

basically I had conditional formatting that coloured each cell either red, amber, green depending on their certificate status. i.e red out of date, amber out of date in a month and green current.

This has disappeared for some reason..

Could you tell me how i would create conditional formatting to:

Colour valid certs - Green
Colour expiring certs - amber
colour out of date red

Thanks!

 

[WarPigl3t]

=now() + 30
=now()

That's all you need to enter. If you have any problems doing it then youtube how to insert highlight conditional formatting because this is beginner stuff.

 

[DanielK2012]

many thanks!