How to set expiration dates with excel to alert for upcoming expirations

Question

I am trying to set up conditional expiration dates for staff files. For example, at my workplace, we have to get physicals every two years. I've input the date of each employees most recent physical completion date. I would like to set something up so that the cell highlights orange when the date is 30 days from expiring and red when it's expired. I've played around with the excel functions and looked a few things up to try to help but can't get anything to work. If possible I would like everything to be based off the current date.

Answer 1

Him

Try this method:

Select the Name column- go to Home- Conditional Formatting- New Rule...- select Use a formula to determine which cells to format- enter the formula and format cell.

30 days from expiring (Orange) : =AND(C1-B1<=30,C1-B1>0)

Expired (Red) : =AND(A1<>0,C1-B1<=0)

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.