Formula Returning Records # of Days from Today in Excel

[Pettitte46]

I have a spreadsheet of employees and each row contains name, hire date,
birthdate, address, etc. The field I'm interested in is the expiration date
of their certification.

I am responsible for obtaining annual re-certification for each employee. I
am not allowed to submit the re-certification until 30 days before it
expires. For instance, if the certification expires 10/01/08 and I submitted
today it would be rejected. I would have to wait until 09/01/08 to submit it.

So what I need is a formula that will return all of the records in the
spreadsheet where the expiration date is less than or equal to 30 days from
today. I want it to automatically calculate from today's date without having
to update anything.

Thanks!

 

[daddylonglegs]

Assume expiration dates are in H2 down. Use this formula in row 2 of a blank
column and copy down

=IF(H2-TODAY()<=30,"x","")

This returns an "x" in the rows of interest. Filter the data by "x"s in this
column

 

[Ragdyer]

You could try Conditional Formatting, where the names can display in Green
when they're within the 30 day window, and turn Red when they're beyond the
expiration date.

Say names are in Column A from A2 to A50, and exp.dates start in D2.

Select all the names (A2 to A50),
then, in the Menu Bar:
<Format> <Conditional Formatting>,
Change "Cell Value Is" to "Formula Is",
And enter this:
=AND(D2<>"",TODAY()>D2)

Click on <Format>, and under the Font tab,
choose Bold and Red.

Then, for the second condition, click on <Add>,
Change "Cell Value Is" to "Formula Is",
And enter this:
=AND(D2<>"",(D2-30)<=TODAY())

Click on <Format>, and under the Font tab,
choose Bold and Green.

Names should now display normally if more then 30 days to expiration,
Green if within the 30 day expiration window,
And Red if beyond the expiration date.