Flagging expiry dates and getting an email sent after the date becomes flagged.

Hello,

I have a worksheet that has hundreds of employees qualifications on it according to when they got certified. Their certification expires every two years so I need a way to automatically flag the expiry date without having to manually look through them all. I was also hoping that upon expiration, Excel could send an email off to myself and flag the expiry date on the spreadsheet in another colour. Anyone have any ideas ?

Answer
Answer

Hi,

each record of the employees  should hold the date of certification and the calculated expiration date based on it:

Expiration date = DATE(YEAR(DateOfCertification)+2,MONTH(DateOfCertification),DAY(DateOfCertification)-1)

Use the conditional formatting to tell you that the certification expires:

  • Select the whole records of the employees
  • Home tab > Conditional Formatting > New Rule > Use a formula to deteremine... > Formula is: =TODAY()>=CellAddressOfExpirationDate
  • Choose a format
  • OK
If the actual date is greater or equal to the expiration date Excel formats the record of the employee in the chosen format.

 

Regards,

Frank


If this post answers your question, please mark it for all readers as the Answer or vote if the reply has been helpful.

If this post answers your question, please mark it as the answer or vote it as helpfull.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

You asked this in "using formulas and equations". 
You could use conditional formatting to highlight rows which have expired (or are close to expiry)

A formula can't send an email; you would need a macro to do that.

You could write a macro to scan down the table looking for rows which have expired (or are close to expiry) and send an email (assuming the email address is in the table).

A good source for information on emailing from Excel is at http://www.rondebruin.nl/sendmail.htm


Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
Microsoft Excel MVP, Oxford, England. www.manville.org.uk

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated September 29, 2020 Views 3,385 Applies to: