Thread: count number of dates in a column that are coming up within 30 days or have gone by

hi, so here's what i believe to be a simple question, yet it eludes me...

I will buy an "excel, how to" book soon, but i figured i'd ask this question ahead of time:

I have a spreadsheet with a list of dates in columns E, F, G.. i need a (1 for each column) formula to check which of those dates are within 30 days of today and tally the number below as well as highlight or change the color on the date in the cell to show which dates are about to expire.. thanks ahead for the help

within 30 days of today

... in the past or in the future?

Regards, TMS

sorry should have clarified, within 30 days of today, in the future (upcoming expiration dates) or expired dates.


thank you

Use Conditional Formatting.

Select all the cells, for example, E2:G200 and use the CF formula/condition: =E2<=TODAY()+30

Format as required.


Regards, TMS

awesome thank you, now is there a way to get it to search e2:g200 and tally up how many certificates are/soon to be expired?

i dont know the commands but basically something along the lines of ( if e2:g200<=today()+30, then count (add 1) ), a simple number would suffice

thanks for your help TMShucks

Sounds like that should be:

=COUNTIF(E2:G200,"<=" & Today()+30)


Regards, TMS

Good grief ... a little scope creep here!

Just qualify the range with the sheet name.

...(Sheet2!E2:G200, ...)


Regards, TMS