How to track expiry dates in a training spreadsheet

DanielK2012

New Member
Joined
Nov 14, 2014
Messages
8
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 :( :mad::confused:
 
Also FYI, sorry if i miss communicated, I can create the formatting I just wasn't sure how complicated the formula had to be ..

Thanks for your help and time
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top